Rows are not getting deleted

Question

The below code is not deleting the rows as expected. Can someone tell me what is wrong with it?

    With Worksheets("Alerts Ack By Cops")
        For rw = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
            Select Case UCase(.Cells(rw, "A").Value2)
                Case "Punna,", "Juleas,"
                    .Rows(rw).EntireRow.Delete
                    i = i - 1
            End Select
        Next rw
    End With

**1.** The title of the question is not for asking question. Use the post body for that. Also be clear in what you are asking and indent your code **2.** Proper tagging is required so that it reaches the right audience. :) Welcome to stackoverflow! — Siddharth Rout, Dec 26 '20 at 07:25
`"Punna,"` and `"Juleas,"` are only part of their cells' text. You need to use `Left` to extract that part of the text before trying to compare them. — Super Symmetry, Dec 26 '20 at 07:25

Siddharth Rout · Answer 1 · 2020-12-26T08:03:25.923

When you are looking for text with capital letter using UCase then you need to match with upper case values.

Change

Case "Punna,", "Juleas,"

to

Case "PUNNA,", "JULEAS,"

Also since you want to match the cell which starts with those words, you can use LIKE as shown below

With Worksheets("Alerts Ack By Cops")
    For rw = .Cells(.Rows.Count, "A").End(xlUp).Row To 2 Step -1
        If UCase(.Cells(rw, "A").Value2) Like "PUNNA,*" Or _
           UCase(.Cells(rw, "A").Value2) Like "JULEAS,*" Then
                .Rows(rw).EntireRow.Delete
        End If
    Next rw
End With

Also, using autofilter would be faster. You may want to see Delete row based on partial text. Here the cirteria becomes "=" & strSearch & "*" instead of "=*" & strSearch & "*"

If you still want to delete the rows using a loop then you may want to see Union way of doing it in Excel VBA - Delete empty rows which will increase the speed drastically over a large number of rows.

score 0 · Answer 2 · answered Dec 26 '20 at 10:59

Delete Rows Using `AutoFilter`

The first solution illustrates the use of two criteria.
The second solution illustrates how to do the same with an array. Unfortunately AutoFilter can have only two criteria containing wild characters. If you add more, nothing will be filtered.
The third solution illustrates a workaround which allows more than two criteria containing wild characters.

The Code

Option Explicit

Sub deleteTwoCriteria()
    Application.ScreenUpdating = False
    With Worksheets("Alerts Ack By Cops")
        .AutoFilterMode = False
        With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
            .AutoFilter _
                Field:=1, _
                Criteria1:="Punna,*", _
                Operator:=xlOr, _
                Criteria2:="Juleas,*"
            With .Resize(.Rows.Count - 1).Offset(1)
                On Error Resume Next
                .SpecialCells(xlCellTypeVisible).EntireRow.Delete
                On Error GoTo 0
            End With
        End With
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

Sub deleteArray()
    Application.ScreenUpdating = False
    With Worksheets("Alerts Ack By Cops")
        .AutoFilterMode = False
        With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
            .AutoFilter _
                Field:=1, _
                Criteria1:=Array("Punna,*", "Juleas,*"), _
                Operator:=xlFilterValues
            With .Resize(.Rows.Count - 1).Offset(1)
                On Error Resume Next
                .SpecialCells(xlCellTypeVisible).EntireRow.Delete
                On Error GoTo 0
            End With
        End With
        .AutoFilterMode = False
    End With
    Application.ScreenUpdating = True
End Sub

Sub deleteList()
    Const CriteriaList As String = "Punna,*|Juleas,*|R2*"
    Dim Criteria() As String: Criteria = Split(CriteriaList, "|")
    Application.ScreenUpdating = False
    With Worksheets("Alerts Ack By Cops")
        .AutoFilterMode = False
        With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
            If .Cells.Count > 1 Then
                Dim Data As Variant: Data = .Value
                Dim dict As Object
                Set dict = CreateObject("Scripting.Dictionary")
                dict.CompareMode = vbTextCompare
                Dim i As Long
                Dim n As Long
                For i = 2 To UBound(Data, 1)
                    For n = 0 To UBound(Criteria)
                        If LCase(Data(i, 1)) Like LCase(Criteria(n)) Then
                            dict(Data(i, 1)) = Empty
                            Exit For
                        End If
                    Next n
                Next i
                If dict.Count > 0 Then
                    .AutoFilter _
                        Field:=1, _
                        Criteria1:=dict.Keys, _
                        Operator:=xlFilterValues
                    With .Resize(.Rows.Count - 1).Offset(1)
                        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
                    End With
                    .Worksheet.AutoFilterMode = False
                End If
            End If
        End With
    End With
    Application.ScreenUpdating = True
End Sub

Rows are not getting deleted

2 Answers2

Delete Rows Using AutoFilter

Delete Rows Using `AutoFilter`