CODEWITHSUNDEEP
excelvba
Kumar
Kumar

Reputation: 1

Rows are not getting deleted

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

sample data

Upvotes: 0

Views: 89

Answers (2)

VBasic2008
VBasic2008

Reputation: 54777

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

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 2

Related Questions