Autofilter Specific Criteria Using VBA

Question

I am using trying to autofilter items using VBA.

The issue is that each list item has slashes in-between them. For example, (ABC/EFG/HIJ). I want to pull out the list items that just contain the string "ABC," whether that is alone or listed along with other string items. For example, if one list item says (ABC/EFG) and another list items says (ABC), I want both of those items, because both of those items contain the ABC string by itself. This is what I have so far:

Sub FilterByABC()

'Change this to the relevant worksheet
Set ws = ThisWorkbook.Sheets("ABC")
Worksheets("ABC").Range("A1").AutoFilter , Field:=1, Criteria1:="ABC"
Operator = xlFilterValues
Range("A1").Select

Columns("A").Copy
Sheets("ABCData").Select
Columns("A").Select
Worksheets("ABCData").Paste


End Sub

It almost worked. However, their are items in the list that begin with ABC EC that I do not want. Any idea how to work around that? — Aub, Apr 28 '17 at 00:38
@Aub [Why accept an answer?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Accepting an answer helps other people with the same question. — M--, May 10 '17 at 22:23

score 1 · Answer 1 · answered Apr 28 '17 at 03:46

Add a second wildcard filter and the Operator:=xlAnd parameter.

Option Explicit

Sub FilterByABC()
    Dim rngDest As Range

    With Worksheets("ABCData")
        Set rngDest = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
    End With

    With Worksheets("ABC")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter Field:=1, Criteria1:="ABC*", _
                        Operator:=xlAnd, Criteria2:="<>ABC EC*"
            With .Resize(.Rows.Count - 1, 1).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    'there are visible, filtered cells in column A; copy then to ABCData
                    .SpecialCells(xlCellTypeVisible).Copy Destination:=rngDest
                End If
            End With
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Autofilter Specific Criteria Using VBA

1 Answers1