Using "wildcards" in a vlist array to delete rows in Excel
- by KMinner
Good Morning All,
I'm trying to setup a vba macro to delete all user IDs out of a spreadsheet that do not start with designated prefixes (e.g. US, A1, VM, etc). The below block of code was found on the Code Library and looks to be what I need but there is one problem: When I enter in UserID prefixes into the vlist fields, it treats them as absolute rather then a part of the string that I want to keep.
Is there a way to incorporate wildcards into a vlist?
Sub Example1()
Dim vList
Dim lLastRow As Long, lCounter As Long
Dim rngToCheck As Range, rngFound As Range, rngToDelete As Range
Application.ScreenUpdating = False
With Sheet1
lLastRow = Get_Last_Row(.Cells)
If lLastRow > 1 Then
vList = Array("US", "A1", "EG", "VM")
'we don't want to delete our header row
With .Range("A2:A" & lLastRow)
For lCounter = LBound(vList) To UBound(vList)
Set rngFound = .Find( _
what:=vList(lCounter), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=True)
'check if we found a value we want to keep
If rngFound Is Nothing Then
'there are no cells to keep with this value
If rngToDelete Is Nothing Then Set rngToDelete = .Cells
Else
'if there are no cells with a different value then
'we will get an error
On Error Resume Next
If rngToDelete Is Nothing Then
Set rngToDelete = .ColumnDifferences(Comparison:=rngFound)
Else
Set rngToDelete = Intersect(rngToDelete, .ColumnDifferences(Comparison:=rngFound))
End If
On Error GoTo 0
End If
Next lCounter
End With
If Not rngToDelete Is Nothing Then rngToDelete.EntireRow.Delete
End If
End With
Application.ScreenUpdating = True
End Sub
