Find Multiple Strings in a Column and Select

[trw88]

Prefix: This is my first post and my first few days into creating macros and using VBA. I've been searching all morning and can't seem to find what I need.

In VBA, I am trying to find multiple strings of text in a certain column (column A in this case) and select all of the cells that contain any of those strings. How would I find and select the cells that contain P1S, P2S, P4S, or P6S? (In this case selecting A2, A4, A5, and A6 all at the same time). I will then run:

Range().Offset(0, 1).Select
Selection.Delete Shift:=xlUp

To delete the selected cells along with the qty's in column B.

Thanks in advance!

 

[Joe4]

Welcome to the Board!

Will the values you be looking for ALWAYS be at the beginning of the entry?
Do you only want to delete columns A and B, or the whole row?
If there is data in other columns, delete just columns A and B could mess up the alignment of your data.

 

[trw88]

Welcome to the Board!

Will the values you be looking for ALWAYS be at the beginning of the entry?
Do you only want to delete columns A and B, or the whole row?
If there is data in other columns, delete just columns A and B could mess up the alignment of your data.

Hi Joe!
The values will always be at the beginning of the entry, but there will not always be exactly 3 characters. The full search I will run is for 32 strings of text.
There is only data in columns A and B, so the entire row could be deleted if that makes more sense.

 

[Joe4]

You can store all the values you want to look for in an array, and loop through them and all rows like this:

Sub MyDeleteRows()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
    
    Application.ScreenUpdating = False

'   Store all values you want to search for in an array
    arr = Array("P1S", "P2S", "P4S", "P6S")
    
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value
            If Left(Cells(r, "A"), Len(x)) = x Then
'               If value found, delete row and exit inner for loop
                Rows(r).Delete
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"

End Sub

 

[trw88]

You can store all the values you want to look for in an array, and loop through them and all rows like this:

Sub MyDeleteRows()

    Dim lr As Long
    Dim arr
    Dim r As Long
    Dim i As Long
    Dim x As String
   
    Application.ScreenUpdating = False

'   Store all values you want to search for in an array
    arr = Array("P1S", "P2S", "P4S", "P6S")
   
'   Find last row with data in column A
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows from bottom to top
    For r = lr To 1 Step -1
'       Loop through each value in array and check for a match
        For i = LBound(arr) To UBound(arr)
'           Get value to look for
            x = arr(i)
'           Check for value
            If Left(Cells(r, "A"), Len(x)) = x Then
'               If value found, delete row and exit inner for loop
                Rows(r).Delete
                Exit For
            End If
        Next i
    Next r

    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"

End Sub

Thank you! Worked exactly as needed.

 

[Joe4]

You are welcome!

Alternatively, you could also store all your options in a list on an Excel worksheet, and loop through those.
The advantage to that approach is if you wanted other users to be able to manage the list of items to look for, but didn't want them messing with the VBA code.