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
– Aub, Commented Apr 28, 2017 at 0:38
@Aub Why accept an answer?. Accepting an answer helps other people with the same question. — M--
– M--, Commented May 10, 2017 at 22:23

user4039065 · Accepted Answer · 2017-04-28 03:46:51Z

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

Collectives™ on Stack Overflow

Autofilter Specific Criteria Using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related