0

Here is a simple script to delete rows that matches a criteria.

From 1 - 10, macro would go through a column 1 and look at the value, and if that value equals to 3, it would delete the entire row.

Sub Test2()
    Dim X As Integer
    For X = 1 To 10
        If Cells(X, 1).Value <> 3 Then
            Rows(X).EntireRow.Delete
        End If
    Next X
End Sub

When I run it, it doesn't behave as I thought,

This is what happened:

Before running Macro
enter image description here

After
enter image description here

not sure why it would leave some entries behind but not others??

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Peter Hui
  • 119
  • 2
  • 9
  • Better not to delete in a loop. Use `Union` to build up a range to delete, or use `Range.AutoFilter` and then delete visible cells. – BigBen Dec 02 '20 at 15:42
  • 1
    If you add or delete in a loop you need to loop backwards `For X = 10 To 1 Step -1` otherwise deleting changes the row counting and it jumps over some rows. • Also note that Excel has more rows than `Integer` can handle. Therefore your variable must be declared `Long`. – Pᴇʜ Dec 02 '20 at 15:44
  • Thank you! I'll look more into the For X = 10 To 1 Step -1. Very interesting and it worked well! – Peter Hui Dec 02 '20 at 15:55

0 Answers0