98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Delete rows
Delete rows
Resolved · High Priority · Version 2010
Mon 6 Feb 2012, 12:06Delegate Ross said...
Ross has attended:
Excel VBA Intro Intermediate course
Delete rows
Hi im trying to create a macro that deletes and entire row if the cells in the range does does not equal a certain criteria
Range C7:C822
Criteria is other
the macro i have written below does not seem to delete all the other rows??
Sub delete()
For Each C In range("C7:C822")
If C <> "Other" Then C.EntireRow.delete
Next
End Sub
regards
Ross
Mon 6 Feb 2012, 15:37Trainer Doug said...
RE: Delete rows
Hi Ross
I've tried your example and it only deletes some of the cells that are not other. If you try changing EntireRow.delete with ClearContents then it does work but leaves gaps for the 'non other' values. The problem is that when an entire row is deleted then the next value of c is missed out. Here's an alternative suggestion for the code.
Range("C7").Select
Do
If ActiveCell.Value <> "other" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell.Value = ""
Note also that "other" is different from "Other" unless you add the line Option Compare Text in the declaration section.
If I find a way using your range variable c I'll let you know.
Cheers
Doug Dunn
Best STL
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Changing the Tab Colour of an Excel 2010 WorksheetDid you know you could give the tabs in your worksheet different colours? |
