Macro for deleting a row with specific criteria in a cell

Closed
Posts
2
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 25, 2013
-
 Blocked Profile -
Hello,

I've got a spreadsheet with many rows and columns, and I'd like to delete specific rows based on criteria I pick. For example, if my criteria is in column FG, and the criteria is the number 1, delete the row. I've got the following macro in VBE, but I have to run it multiple times for it to delete all the appropriate rows. Can someone help me debug it? Thanks.

Sub DeleteRows()

Dim MyCol As String
Dim MyVal As Variant
Dim i As Integer

MyCol = InputBox("Column to evaluate?", "Column Search", "FG")
MyVal = InputBox("Value to look for", "search value", 1)
For i = 1 To Range(MyCol & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":FZ" & i), MyVal) > 0 Then
Range("A" & i).EntireRow.Delete
End If
Next i

End Sub

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
when you write a macro for deleting rows you loop from last row to first row and not reverse . rewrite macro
Posts
2
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 25, 2013

That's just the point... I don't know how to rewrite the macro. I found the one I'm using in another post. Any chance you could give me the code for the looping from last to first?

Good Morning.

If you just want to do as the previous poster had mentioned change the following lines in your macro:

MyCol = InputBox("Column to evaluate?", "Column Search", "FG")
MyVal = InputBox("Value to look for", "search value", 1)
For i = Range(MyCol & "65536").End(xlUp).Row to 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A" & i & ":FZ" & i), MyVal) > 0 Then
Range("A" & i).EntireRow.Delete
End If


Hope this gets you closer.