• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

List of Filtered Columns needed

Tyler

New Member
Hi -
I'm building a template for inventory analysis. I currently have a file with columns A-CO (93 columns) and the rows will be up to 40,000 depending on the user needs.

Filters are available on every column. In most cases only a handful of columns (1-10) will be filtered at a time.​

I'm trying to create a list of filtered columns so the user can easily see which fields are filtered (rather than scrolling through the 93 columns to find them).

What I've tried:
Pivot tables and slicers are nice, but do not work well with a large number of columns.
I googled the question and reviewed many sites/blogs but I did not find a solution.

Any help is appreciated!
Thanks,
Tyler
 
I would suggest using a custom function, ie a UDF. Right-click on a sheet tab, view code. Go to Insert - Module. Paste this in:
Code:
Function FiltersOn(r As Range) As String
Dim ws As Worksheet
Set ws = r.Worksheet
Dim ft As Filter
Dim i As Long
Dim colStr As String
Dim fCheck As Boolean

fCheck = False

Application.Volatile
i = 1
With ws
    If .AutoFilterMode Then
        For Each ft In .AutoFilter.Filters
            If ft.On Then
                fCheck = True
                colStr = .Cells(1, i).Address(False, False)
                FiltersOn = FiltersOn & ", " & Left(colStr, Len(colStr) - 1)
            End If
            i = i + 1
        Next ft
        If fCheck Then
            FiltersOn = "Filters in: " & Mid(FiltersOn, 3)
        Else
            FiltersOn = "No filters"
        End If
    Else
        FiltersOn = "No filters"
    End If
End With
End Function

On your worksheet with the table, find a cell somewhere, and put this formula:
=FiltersOn(A1)
This will now display what columns (if any) have filters on them

Example:
upload_2015-1-26_14-13-25.png
 
Hi Luke,
Thank You! I have played with some macros in the past, but never functions. This works great! Thank you very much!! I will use it, step through it to get a better understanding of how the code works and learn from it.
Thanks again!
Tyler
 
Back
Top