How to delete rows based on condition in VBA? [duplicate]

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
– Siddharth Rout, Commented Oct 3, 2014 at 9:06

Gareth · Accepted Answer · 2014-10-03 09:10:00Z

0

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

answered Oct 3, 2014 at 9:10

Gareth

5,2636 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete rows based on condition in VBA? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related