Deleting Certain Rows Using InStr

[himalayan]

Hi there,

I'm trying to delete specific rows in a range that do not contain the value input by a user, almost like a filter:

User clicks command button and enters value -> Range ("C2:C25") is queried for the value -> Any cells that do not contain the value are deleted (as a whole row)

The problem I am having is how the loop controls the deleting of rows, which is forcing it to miss out rows if there are to be two deleted rows in succession. I am hoping someone can give me an idea as to how I can get around this. I've got to the point where my mind has stopped working because I've been fussing about it for so long

Do Until CurrentRow = LastRow + 1If InStr(1, Range(FilterField & CurrentRow), FilterValue, vbTextCompare) Then
CurrentRow = CurrentRow + 1
Else
.Rows(CurrentRow).EntireRow.Delete
CurrentRow = CurrentRow + 1
End If
Loop
End With

Thanks

 

[SteveO59L]

you need to delete from the last row upover

Dim LR As Integer

LR = Cells(Rows.Count, "a").End(xlUp).Row
For i = LR To 2 Step -1
If Range("a" & i) = "" Then
Range("a" & i).EntireRow.Delete
End If
Next i