If Text Not Found Delete Entire Row

[indi visual]

For the life of me I have no idea why this isn't working.


'''if any cell in column D does not contain "*:*" then delete entire row

Dim C As Range

For Each C In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)

If C.Text <> "*:*" Then

C.EntireRow.Delete Shift:=xlUp

End If

Next C

 

[Hui]

[pre]

Dim x As Integer
For x = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Rows.Count + 1 To 2 Step -1
If Not (Cells(x, 4).Text Like "*:*") Then Cells(x, 4).EntireRow.Delete Shift:=xlUp
Next x

[/pre]

It is advisable when deleting rows to work from the Bottom to the top

 

[Luke M]

To expand on Hui's post, the reason you'd want to work bottom to top is because when you delete a row, XL shifts the rows up (so if you delete row 3, what was row 4 is now row 3.) However, since the code already looked at row 3 and deleted it, it goes to row 4 (which was previously row 5).

 

[indi visual]

Many thanks guys.


I will remember to work from the bottom up when deleting rows in the future.