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
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
