How to delete rows based on condition in VBA?

Question

I have a table, where in C column are numeric values sorted from max to min. If C column values are > 40, the macro deletes an entire table row. I'm using following macro:

Sub DeleteRowsPiuDi40Mega()
Dim LastRow As Long
Dim ws4 As Worksheet
Set ws4 = ActiveWorkbook.Sheets("atm_hh")
LastRow = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 3) > 40 Then Rows(i & ":" & i).EntireRow.Delete
Next i
End Sub

The problem is that after deleting some rows it stops, still leaving in column C some values that are > 40. What could be wrong with this code?

You have to delete the rows in reverse `For i = LastRow to 2 Step -1` so that it doesn't skip rows. Also what you are trying to achieve has already been answered. Let me search the link for you — Siddharth Rout, Oct 03 '14 at 09:06
You can use [THIS](http://stackoverflow.com/questions/9379673/excel-vba-delete-empty-rows) method. — Siddharth Rout, Oct 03 '14 at 09:10

score 0 · Accepted Answer · answered Oct 03 '14 at 09:10

The issue is that once a row has been deleted, the macro will continue onto the next row (skipping a row).

Working backwards through the loop will prevent any rows being missed so something like:

Sub DeleteRowsPiuDi40Mega()

Dim LastRow As Long
Dim ws4 As Worksheet

Set ws4 = ActiveWorkbook.Sheets("atm_hh")
LastRow = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row

For i = LastRow to 2 Step -1
    If Cells(i, 3) > 40 Then Rows(i & ":" & i).EntireRow.Delete
Next i

End Sub

How to delete rows based on condition in VBA?

1 Answers1

Linked