Deleting a Row in Excel if Value is less than X

Question

I'm trying to delete a row in excel if it meets the criteria that the value in Column B is less than 100. I saw this and tried to follow it, but I'm not that knowledgeable in VBA, so I'm not sure where to go.

EDIT This is the code I've tried thus far, and it does not work.

Sub DeleteR()
 Dim myRow As Range
 Dim toDelete As Range

 For i = 2 To 5000
    If Worksheets("PalCountPLTZR").Cells(i, 2) < "99" Then
    Set myRow = Worksheets("PalCountPLTZR").Rows(i)
    If toDelete Is Nothing Then
        Set toDelete = myRow
    Else
        Set toDelete = Union(toDelete, myRow)
    End If
End If
Next i

End Sub

I'm not sure why it does not work, but I'm wondering if the cells that its looking at are from a query.

Here are some other methods; https://stackoverflow.com/questions/33744149/code-in-vba-loops-and-never-ends-how-to-fix-this Try these and when you get stuck please bring the code you tried and explain how you adjusted it and why it is not working. — Scott Craner, Aug 14 '17 at 13:15

score 2 · Accepted Answer · answered Aug 14 '17 at 13:41

2

You can use this loop to check and delete rows in column "A":

Sub DeleteRows()
    'get last row in column A
    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
        'if cell value is less than 100
        If (Cells(i, "A").Value) < 100 Then
            'delete entire row
            Cells(i, "A").EntireRow.Delete
        End If
    Next i
End Sub

answered Aug 14 '17 at 13:41

M_Idrees

2,080
2
23
53

Actually, I got it to work, just didn't need the "" around the value I was searching for... Thanks! – Lampoa Aug 14 '17 at 13:45
Your values are in `A` column ? – M_Idrees Aug 14 '17 at 13:45

score 0 · Answer 2 · answered Aug 14 '17 at 13:19

Ed showed you the .Delete portion, in the post you linked:

Chris Nielsen's solution is simple and will work well. A slightly shorter option would be...

ws.Rows(Rand).Delete

In addition to that knowledge, you would need to add an if-statement to show the criteria to delete, such as:

If Cells(i,2)="x" Then
    Rows(i).Delete
    End If

This approach would use a loop for i, so you will need to read up on that.

Good luck, and in the future, put the code you've tried, specifically how you've tried to implement if you've found other code online, so we can try to help fix problems.

score 0 · Answer 3 · answered Aug 14 '17 at 13:35

0

Two things,

"99" should be just 99

And you never do the deletion at the end.

Sub DeleteR()
 Dim myRow As Range
 Dim toDelete As Range

 For i = 2 To 5000
    If Worksheets("PalCountPLTZR").Cells(i, 2) < 99 Then
    Set myRow = Worksheets("PalCountPLTZR").Rows(i)
    If toDelete Is Nothing Then
        Set toDelete = myRow
    Else
        Set toDelete = Union(toDelete, myRow)
    End If
End If
Next i
If Not toDelete Is Nothing Then toDelete.EntireRow.Delete
End Sub

answered Aug 14 '17 at 13:35

Scott Craner

148,073
10
49
81

I got an error message "1004 delete method of class range failed" with "toDelete.EntireRow.Delete" – Lampoa Aug 14 '17 at 13:40
I get no such error. I tried many things to try an reproduce the error, but can't. check to ensure you do not have typos in that line. – Scott Craner Aug 14 '17 at 13:45

Deleting a Row in Excel if Value is less than X

3 Answers3