0

I've been working on a project and i am at the last step, what i want the last piece of code to do is delete all rows that do not start with 25, 26 or 27

For Each cell In Worksheets("Sheet2").Range("A:A")
        If Left(CStr(cell.Value), 2) <> 25 And Left(CStr(cell.Value), 2) <> 26 And Left(CStr(cell.Value), 2) <> 27  Then
            matchRow = cell.Row
            Rows(matchRow & ":" & matchRow).Select
            Selection.Delete Shift:=xlDown

The results are not as hoped: some cells still remain after running it. Ii think that with this method, when I delete a cell it shifts the cycle and it skips the next cell over.

How can i prevent this from happening?

user3614214
  • 1
  • 4

1 Answers1

1

For deleting rows, it is good to start at the bottom and work up

Dim cell As Range
Dim lastrow As Long
Dim counter1 As Long

lastrow = Cells.Find(what:="*", searchorder:=xlByRows, 
searchdirection:=xlPrevious).Row

For counter1 = lastrow To 1 Step -1
    Select Case Left(CStr(Cells(counter1, 1).Value), 2)
        Case 25, 26, 27
            Cells(counter1, 1).EntireRow.Delete
    End Select
Next counter1
bilbo_strikes_back
  • 591
  • 3
  • 10
  • 1
    Just some “add-ons”: 1) “Cells(counter1, 1).EntireRow.Delete” could be simplified to “Rows(counter1).Delete” 2) “Case 25, 26, 27” could be simplified to “Case 25 To 27” – DisplayName Sep 12 '18 at 16:02
  • Note: `Cells.Find(what:="*", ...)` will return `Nothing` and make the chained `.Rows` member call blow up (error 91) if used on an empty sheet. See [this answer](https://stackoverflow.com/a/11169920/1188513) for a bullet-proof solution. Also, iterating cells like this is *slow*, and inefficient regardless of whether `Application.EnableEvents` is enabled (although disabling application events can make it complete faster) - there are better ways. – Mathieu Guindon Sep 12 '18 at 17:29
  • Not just "good" to start at the bottom and work up, it's the only reliable way (if you're looping through the rows...) – FreeMan Sep 12 '18 at 17:31