Using "wildcards" in a vlist array to delete rows in Excel

Question

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

Answer 1

Interesting - I've never noticed the ColumnDifferences method before, so thanks for that.

Being unfamiliar with it, I couldn't quite figure out what you were trying to do with this macro.

If you are checking the contents of column A to see if the first two characters in each cell match the values in your array, and if it does you delete the entire row, you could accomplish that with a loop as follows:

Sub Example2()

    Dim lLastRow As Long
    Dim lCounter As Long

    Application.ScreenUpdating = False

    With Sheet1

        lLastRow = Get_Last_Row(.Cells)

        If lLastRow > 1 Then

            For lCounter = lLastRow To 2 Step -1

                Select Case Left(.Cells(lCounter, 1).Value, 2)
                    Case "US", "A1", "EG", "VM"
                        .Cells(lCounter, 1).EntireRow.Delete
                End Select

            Next lCounter

        End If

    End With

    Application.ScreenUpdating = True

End Sub

Note that when you are looping through and deleting rows, you need to start from the last row and move up.

If this isn't quite what you're trying to do, let me know and I will try again :)