VBA Deleting rows with the Like operator

Question

Simple thing is not simple. I am trying to delete rows based on a specific column having data that begins with "2L". So I wrote this code (LastRow is understood):

Sub Cleanup()

For i = 1 To LastRow
    If Range("F" & i) Like "2L*" Then Rows(i).delete
Next i
End Sub

The problem is that it does delete rows. I have no idea what it's criteria is for deleting rows but I know it does not get every row with a cell that starts with "2L" in that column as it was written. I start out with 1100 row and it goes down to 677.

I did not create the excel that I am scripting in. the only clue I have is that I did try to format the column and when the format window opens, they don't have a single type. I did try formatting the column of data before I ran the code to be Text but it did not seem to do the trick.

Thoughts?

Answer 1

First, some mandetory house keeping.....

1. Add Option Explicit
2. Add Option Explicit
3. Qualify all objects (Range) with a worksheet

---

You should avoid deleting rows as you loop for a few reasons. The main reason is that it can be highly inefficient. Say, for instance, that you have 500 cells that are Like "2L*". That means you will have 500 iterations of rows being deleted.

Instead, add every instance of Like "2L*" to a Union (collection) of cells and once your loop is complete, delete the entire Union all at once. Now you just have 1 instance of rows being deleted.

Another reason to avoid deleting rows inside your loop is that it forces you to loop backwards. There is nothing wrong with this, it just tends to give people a hard time since it is not intuitive at first. When you delete a row, you shift the range you are looping through up and this causes rows to be skipped. The method below does not need to be looped backwards.

---

Option Explicit

Sub Cleanup()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("???")
Dim i As Long, DeleteMe As Range

'Gather up union of CELLS like 2L*
For i = 1 To ws.Range("F" & ws.Rows.Count).End(xlUp).Row
    If ws.Range("F" & i) Like "2L*" Then
        If DeleteMe Is Nothing Then
            Set DeleteMe = ws.Range("F" & i)
        Else
            Set DeleteMe = Union(DeleteMe, ws.Range("F" & i))
        End If
    End If
Next i

'Delete collection of cells here (if any exist)
If Not DeleteMe Is Nothing Then DeleteMe.EntireRow.Delete

End Sub

---

For learning purposes, this is how you would loop backwards. It has less lines than the above method, but is less efficient. If you do decide to go this route, be sure to toggle off ScreenUpdating to speed things up

Sub CleanUp2()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("???")
Dim i As Long

For i = ws.Range("F" & ws.Rows.Count).End(xlUp).Row to 1 Step -1 '<===== Backwards!
    If ws.Range("F" & i) Like "2L*" Then ws.Rows(i).Delete
Next i

End Sub

Answer 2

I'll borrow from Jeeped here and offer an answer that doesn't require a loop:

Sub DeleteBlankRows()

    With Worksheets(1)
        On Error Resume Next
        Intersect(.Range("F:F"), .UsedRange).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
    End With

End Sub

Answer 3

Or use autofilter

Option Explicit
Public Sub CleanUp()
    Dim lastRow As Long, testRange As Range
    With ThisWorkbook.Worksheets("Sheet2")
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        Set testRange = .Range("F1:F" & lastRow)
        If Application.WorksheetFunction.CountIf(testRange, "2L*") > 0 Then
            With testRange
                .AutoFilter
                .AutoFilter Field:=1, Criteria1:="2L*", Operator:=xlFilterValues
                .SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With
        End If
    End With
End Sub