Find Multiple Strings in a Column and Select

trw88

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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.

1617649587464.png


Thanks in advance!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
You can store all the values you want to look for in an array, and loop through them and all rows like this:
VBA Code:
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
 
Solution

trw88

New Member
Joined
Apr 5, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
You can store all the values you want to look for in an array, and loop through them and all rows like this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,923
Messages
5,639,024
Members
417,063
Latest member
ShijinMathew

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top