Shorten/simplify/improve/make more efficient this bit of code

[Truiz]

Good day lads,

I have this code

For iCntr = lRow To 1 Step -1                                           If Cells(iCntr, "L") = "Close" Then                                 
    Rows(iCntr).Delete                                                  
    End If                                                              
    Next                                                                
                                                                        
    For iCntr = lRow To 1 Step -1                                       
    If Cells(iCntr, "L") = "Close Supervised + Auto Notify" Then        
    Rows(iCntr).Delete                                                  
    End If                                                              
    Next                                                                
                                                                        
    For iCntr = lRow To 1 Step -1                                       
    If Cells(iCntr, "L") = "Fail to Close" Then                         
    Rows(iCntr).Delete                                                  
    End If                                                              
    Next                                                                
                                                                        
    For iCntr = lRow To 1 Step -1                                       
    If Cells(iCntr, "L") = "Fail To Open" Then                          
    Rows(iCntr).Delete                                                  
    End If                                                              
    Next

I wrote it as such cuz i'm no expert in VBA I was wondering if there is a way to shorten the code I mean it works and everything I just think in looks dirty to have that.

Regards,

 

[Fluff]

Untested but how about

    Dim Arr() As Variant

    Set Arr = Array("Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open")

    For iCntr = lrow To 1 Step -1
        If UBound(Filter(Arr, Cells(iCntr, "L"), , vbTextCompare)) >= 0 Then
            Rows(iCntr).Delete
        End If
    Next

 

[Rick Rothstein]

Here is another way to consider...

[table="width: 500"]
[tr]
	[td]For iCntr = lRow To 1 Step -1
  Select Case Cells(iCntr, "L")
    Case "Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open"
      Rows(iCntr).Delete
  End Select
Next[/td]
[/tr]
[/table]

Note: You spelled the word "to" differently in "Fail to Close" and "Fail To Open". I used the spelling you specified but would point out that letter casing for the text in your cells is important and must exactly match the text used in the code above. If you cannot be sure the text will always be entered exactly the same way, then use the following version of my code instead...

[table="width: 500"]
[tr]
	[td]For iCntr = lRow To 1 Step -1
  Select Case LCase(Cells(iCntr, "L"))
    Case "Close", "close supervised + auto notify", "fail to close", "fail to open"
      Rows(iCntr).Delete
  End Select
Next[/td]
[/tr]
[/table]

 

[JoeMo]

Another way:

Sub Truiz()
Dim Unwanted As Variant, lRow As Long, j As Long
Unwanted = Array("Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open")
lRow = Range("L" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Range("L1:L" & lRow)
    For j = LBound(Unwanted) To UBound(Unwanted)
        .Replace Unwanted(j), "#N/A", xlWhole
    Next j
End With
On Error Resume Next
Range("L1:L" & lRow).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[JackDanIce]

A fourth suggestion, using filter instead of loop:

Sub Remove()

    Dim x           As Long
    Dim strFiler    As Variant
    
    strfilter = Array("Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open")
    
    With ActiveSheet
        x = .Cells(.Rows.count, 12).End(xlUp).row
        With .Cells(1, 12).Resize(x)
            On Error Resume Next
            .AutoFilter Field:=1, Criteria1:=strfilter, Operator:=xlFilterValues
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False
    End With
    
    Erase strfilter
    
End Sub

As you can see, depending on your preference "Simplify/Improve/Make more efficient" can lead to lots of suggestions!

 

[Truiz]

Here is another way to consider...

[TABLE="width: 500"]
<tbody>[TR]
[TD]For iCntr = lRow To 1 Step -1
  Select Case Cells(iCntr, "L")
    Case "Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open"
      Rows(iCntr).Delete
  End Select
Next[/TD]
[/TR]
</tbody>[/TABLE]

Note: You spelled the word "to" differently in "Fail to Close" and "Fail To Open". I used the spelling you specified but would point out that letter casing for the text in your cells is important and must exactly match the text used in the code above. If you cannot be sure the text will always be entered exactly the same way, then use the following version of my code instead...

[TABLE="width: 500"]
<tbody>[TR]
[TD]For iCntr = lRow To 1 Step -1
  Select Case LCase(Cells(iCntr, "L"))
    Case "Close", "close supervised + auto notify", "fail to close", "fail to open"
      Rows(iCntr).Delete
  End Select
Next[/TD]
[/TR]
</tbody>[/TABLE]

I never noticed that I got the list from the original report I guess luckily it worked

 

[Truiz]

A fourth suggestion, using filter instead of loop:

Sub Remove()

    Dim x           As Long
    Dim strFiler    As Variant
    
    strfilter = Array("Close", "Close Supervised + Auto Notify", "Fail to Close", "Fail To Open")
    
    With ActiveSheet
        x = .Cells(.Rows.count, 12).End(xlUp).row
        With .Cells(1, 12).Resize(x)
            On Error Resume Next
            .AutoFilter Field:=1, Criteria1:=strfilter, Operator:=xlFilterValues
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False
    End With
    
    Erase strfilter
    
End Sub

As you can see, depending on your preference "Simplify/Improve/Make more efficient" can lead to lots of suggestions!

EXACTLY what I wanted looking at how others tackle the task does lead to lots of suggestion suggestions that at one point will all become useful as I always say Information is the one thing you can't have to much of

 

[Truiz]

I encountered an issue.

One of the strings I want to delete is AR/Billing "Insurance" as you can see it has quotation marks, thus creating a syntax error

 

[JackDanIce]

Instead, could you delete anything that begins "AR/Billing*"with use of a wild card?

 

[Truiz]

Instead, could you delete anything that begins "AR/Billing*"with use of a wild card?

Somehow double quotations decided to work properly all of the sudden