0

I have searched and searched for an answer to this, and have found many variations, but can't figure out how to loop through all filters on a table column. Example: Loop through each filterable value in column A, to perform a function on the visible cells. I have found ways to do so when setting the column filter criteria to something specific (I.E 'Cat'), but I want to go through each one by loop.

Can this be done by referencing the filter values by index or something similar? Below is the code I have found to loop through the visible cells, but I need to add a preceding loop to adjust the filters on that specific column...

Any help is appreciated!

Sub TestFilteredTable()

   Dim tbl As ListObject
   Dim rngTable As Range
   Dim rngArea As Range
   Dim rngRow As Range

   Set tbl = ActiveSheet.ListObjects(1)
   Set rngTable = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

   'Filtered Table Address
   Debug.Print "Filtered table: " & rngTable.Address

   'Add Loop Here to iterate through specific column filter items

       'Loop through each area
       For Each rngArea In rngTable.Areas
          Debug.Print "  Area: " & rngArea.Address

             'Loop through visible rows
             For Each rngRow In rngArea.Rows
                Debug.Print "    Row: " & rngRow.Address
             Next
       Next

    'Close filter loop

End Sub
Community
  • 1
  • 1
Allen
  • 56
  • 2
  • 14
  • If I understand the question correctly then there are ample of related questions / solutions to get the code from: http://stackoverflow.com/questions/29773987/get-all-list-of-possible-filter-criteria OR http://stackoverflow.com/questions/7764655/get-autofilter-sort-criteria-and-apply-on-second-sheet OR http://stackoverflow.com/questions/9489126/in-excel-vba-how-do-i-save-restore-a-user-defined-filter So, what's the hold-up? Can you elaborate a bit more what the problem is? Maybe I don't understand and a few screenshots + sample data can help clarify? – Ralph Mar 23 '17 at 19:06
  • I have previously reviewed those and did not see a way to do what I am looking to do. The first address you listed appears to go through each item in the column range after it is already filtered. The second item only returns results if the column has a filter applied, and will advise what items are currently checked. The third item tells you what the current filter is and allows you to remove/reset it. – Allen Mar 23 '17 at 20:01
  • I am looking to actually change the 'column a' filter by loop through each item in the filter. So if I have a list of items in column a of: cat, dog, fish, taco, fish, cat, cat, soup, my filter options it would iterate though would be cat, dog, fish, taco, soup. I will need to grab all of the results in each filter of that item. (All of the Cats, then all of the fish... etc.) – Allen Mar 23 '17 at 20:01
  • Why don't you just grab all of them at once and then sort them by cats, dogs, fish, etc. (which would be an alphabetic order)? – Ralph Mar 23 '17 at 20:05

0 Answers0