CODEWITHSUNDEEP
vbaexcelfiltercompare
Alberto Brown
Alberto Brown

Reputation: 355

excel macro to cycle through filtering options

In excel File A, Col A i have family type for a parts list. 0.5V, 2V, 5V,10V,12V, etc in Col B thru K I have some part data dimensions and things like that. I was looking for a way to have a macro run a filter by the values in Col A, then run other code I already have, clear the filter selection, and then go to the selection option in the filter.

so if my filter options were

.1
.5
.03
.9
2
8

it would filter for .1 , run code, clear filter, filter by .5, run code, clear filter, filter by .03 etc etc. I am total lost on how to cycle thru the filter list. Any help would be much appreciated

Upvotes: 1

Views: 662

Answers (1)

user4039065
user4039065

Reputation:

create an array of the values then cycle through that array.¹

Sub sequentialAutoFilter()
    Dim a As Long, arrs As Variant

    arrs = Array(0.1, 0.5, 0.03, 0.9, 2, 8)

    With Worksheets("Sheet99")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            For a = LBound(arrs) To UBound(arrs)
                .AutoFilter field:=1, Criteria1:=arrs(a)
                With .Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0)
                    If CBool(Application.Subtotal(103, .Cells)) Then
                        'there are filtered cells visible, run code here
                        'note: .Cells does not include the header row here
                    End If
                End With
            Next a
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With

End Sub

¹ Quoted from Scott Craner's comments.

Upvotes: 1

Related Questions