Macro to Delete rows based on criteria

[tjc154]

I need a macro that removes all rows in column A that contain the following text string in the cell.

AUML
AZCF
AZEF
AZGR
UKRCDR
SGSG

Thanks,

Tom

 

[xld]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A"       '<<<< change to suit
Dim Lastrow As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
        For i = Lastrow To 1 Step -1
        
            If Cells(i, TEST_COLUMN).Value2 Like "*AUML*" Or _
                Cells(i, TEST_COLUMN).Value2 Like "*AZCF*" Or _
                Cells(i, TEST_COLUMN).Value2 Like "*AZEF*" Or _
                Cells(i, TEST_COLUMN).Value2 Like "*AZGR*" Or _
                Cells(i, TEST_COLUMN).Value2 Like "*UKRCDR*" Or _
                Cells(i, TEST_COLUMN).Value2 Like "*SGSG*" Then
            
                .Rows(i).Delete
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True
End Sub

 

[tjc154]

Thank you that worked perfectly.

 

[tjc154]

Can you clarify what you mean by Select Case structure?

 

[xld]

Where did I say that?

 

[tjc154]

I created a separate thread for multiple line continuations. My response was to the previous thread. The site admin closed out the thread and asked that I post to this thread. Please disregard.

Thanks,

Tom

 

[tjc154]

xld,

I did have one question. Since I am unable to exceed more than 24 statements, I would like to duplicate the code you provided and run this as single macro. I have a total of 42 statement, so I would essentially replicate the code for statements 25-42, but just modify the search string values.

Cells(i, TEST_COLUMN).Value2 Like "*ABCD*" Or _

How do I create this as a single macro? I thought using a Call function would work, but it makes sense, its a separate sub routine.

Thanks,

Tom