Macro for deleting a row with specific criteria in a cell

Closed
MacroNovice Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 25, 2013 - Jun 18, 2013 at 06:13 PM
 Blocked Profile - Jun 26, 2013 at 09:08 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 19, 2013 at 09:25 PM
when you write a macro for deleting rows you loop from last row to first row and not reverse . rewrite macro
0
MacroNovice Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 25, 2013
Jun 25, 2013 at 12:07 PM
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?
0
Blocked Profile
Jun 26, 2013 at 09:08 AM
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.
0