Results 1 to 4 of 4

VBA:Getting a list of available criteria using autofilter

This is a discussion on VBA:Getting a list of available criteria using autofilter within the Excel Questions forums, part of the Question Forums category; I am new to the board but have spent a lot of time "living inside" Excel VBA code. I am ...

  1. #1
    New Member
    Join Date
    Mar 2006
    Posts
    2

    Default VBA:Getting a list of available criteria using autofilter

    I am new to the board but have spent a lot of time "living inside" Excel VBA code. I am trying to find VBA code that lists or "gets" the criteria for each column once autofilter is on.

    Is there a VBA function that can do this? Is there a way to "loop" through each criteria and store in an array?

    Mike

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default

    This is a slight modification to the HELP sample for the Object (you need to have active filters, that is you need to select a criteria from at least one AutoFilter to use this code):


    Dim w As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String

    Sub ChangeFilters()
    Dim myKs

    Set w = Worksheets("Sheet1")

    With w.AutoFilter
    currentFiltRange = .Range.Address

    With .Filters
    ReDim filterArray(1 To .Count, 1 To 3)

    For f = 1 To .Count
    With .Item(f)
    myKs = myKs & "Filter: " & f & " "

    If .On Then
    filterArray(f, 1) = .Criteria1
    myKs = myKs & "Criteria1: " & filterArray(f, 1) & " "

    If .Operator Then
    filterArray(f, 2) = .Operator
    myKs = myKs & "Operator: " & filterArray(f, 2) & " "

    filterArray(f, 3) = .Criteria2
    myKs = myKs & "Criteria2: " & filterArray(f, 3) & " "

    End If
    End If
    End With
    myKs = myKs & " " & vbLf
    Next f
    End With
    End With
    MsgBox myKs

    End Sub
    JSW: Try and try again: "The way of the Coder!"

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,984

    Default Re: VBA:Getting a list of available criteria using autofilte

    spent a lot of time "living inside" Excel VBA code.
    Wow - - wonder if that has any long-term effects.

    I am trying to find VBA code that lists or "gets" the criteria for each column once autofilter is on.
    You may find this thread useful:
    http://www.mrexcel.com/board2/viewtopic.php?t=158947

  4. #4
    New Member
    Join Date
    Mar 2006
    Posts
    2

    Default Re VBA:Getting a list of available criteria using autofilter

    Thanks for the reply.

    I was hoping for a way to identify the critier without having to go through and select each one.

    The thread link you provided gives me some ideas.

    I suspect I will have to loop through the actual list and copy unique entries to another list.

    Thanks for the help, and yes - living inside excel does have long term effects - I always seem to want to select active sheets - lol

    Mike

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com