Interrogating the excel VBA 'filters' object

Question

0.00/5 (No votes)

See more:

Excel-2016

I need to interrogate the Filters object to learn what columns have active filters (Filter.On = True) and what the filter criteria are for each filter.
The problem that I have encountered is that the Filters object does not contain any information about which column each Filters.Item relates to.
There is no real problem if all columns have been ‘enabled’ for filters (i.e. each column header cell has a down-pointing arrow), because Filters.Count = the number of columns in the worksheet, and I can easily associate each filter with its relevant column.
But when some (but not all) columns have been ‘enabled’ for filters (for example columns 4-8), I have not been able to discover an algorithm for correlating each of the 5 filters with its relevant column.
Can the hive mind I know and love as Code Project please help me.

What I have tried:

I have tried everything I can think of!

Posted 19-Jul-21 20:49pm

captaincoder2

Updated 20-Jul-21 22:35pm

Add a Solution

Comments

Richard MacCutchan 20-Jul-21 4:12am

Just guessing here, but it is possible that the filters do not need to know which column they are associated with. It is only necessary that the column knows which filter it is using.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2021-07-20T10:14:00

I'm pretty sure that Filters.Count refers to the number of filters on a specific column not the number of columns included in a filter. This code will list the columns that do have a filter applied, and what those criteria are

VB

 With ThisWorkbook.Sheets(1).AutoFilter
     Dim l As Long
     For l = 1 To .Filters.Count
         Debug.Print "Column ", .Range(1, l).Column, .Range(1, l).Address,
         Debug.Print .Filters(l).On,
         If .Filters(l).On Then
             Debug.Print .Filters(l).Count,
             If .Filters(l).Count > 1 Then
                 Dim slist As String
                 slist = Join(.Filters(l).Criteria1, ", ")
             Else
                 slist = .Filters(l).Criteria1
             End If
             Debug.Print slist,
         End If
         Debug.Print ""
     Next
End With