Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have a sheet with a huge number of columns and filtering is often used to navigate to the correct data. The problem is that sometimes its not obvious that the filter has been applied , the visual cue is very subtle. Is it possible to show some data via a formula or VBA about the filter inside another cell?

Something like this:

Just knowing if the filter is active would be a good help, knowing what columns have active filters applied to them would be icing on the cake. Ideally they update automatically.

I dont have ownership of the spreadsheet so cant make major changes to its structure or anything but VBA is fine.

Any ideas?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

I believe this is possible only with VBA.

I would also assume that you don't need to check if the autofilter is applied on the correct range (see Further Reading), and that you don't have to check if AUTOFILTER is active or not (i.e. no arrows, no autofilter).

Here's a user-defined function that checks if there are any active filters in a worksheet and, if there are any, displays which columns have been filtered. I'm sure there are better functions out there; you may need to modify it to suit your needs.

Function CheckFilters(r As Range) As String

Set AWS = ActiveSheet
fstate = ""

If AWS.FilterMode Then
    c = AWS.AutoFilter.Filters.Count

    'go through each column and check for filters
    For i = 1 To c Step 1
       If AWS.AutoFilter.Filters(i).On Then
            fstate = fstate & r(i).Value & ", "
       End If
    Next i

    'removes the last comma
    fstate = Left(fstate, Len(fstate) - 2) 
Else
    fstate = "NO ACTIVE FILTERS"
End If

CheckFilters = fstate

End Function

To use it, enter the ff. formula into an empty cell:

=CheckFilters(A3:E3)&LEFT(SUBTOTAL(9,A3:A48),0)

Where A3:E3 contains the column labels for your data (see example below)

The &LEFT(SUBTOTAL(9,A3:A48),0) portion of the formula serves no visible purpose other than to force the cell to re-evaluate "automatically" as you change the filters. (Credit to Sean Cheshire for suggesting it). Note this is better than the solution described here because it only recalculates when the range you have defined in your parameters changes.

EXAMPLES

With Filters:
enter image description here

AutoFilter is Enabled but No Columns are Filtered:
enter image description here

Used with Conditional Formatting:
enter image description here

FURTHER READING

How to use AutoFilters in Excel VBA Macros
AutoFilter Object (Excel 2003 VBA Language Reference)

share|improve this answer
    
Fantastic. Only issue is the update of fstate. If I change the filter, the filter string does not update. I can only seem to trigger an update by changing the CheckFilters formula in the cell. Is there some setting to force this function to evaluate when the filter is changed? –  Alex Jun 14 '12 at 23:36
    
I confirmed by putting a breakpoint in the VBA that the function is only being triggered when the CheckFilters cell is modified (say by adjusting the range). Changing the filter does not trigger the function. –  Alex Jun 14 '12 at 23:39
    
@Alex I'll look into autoupdating the formula. You can try changing function into a subroutine that executes when the Workbook_SheetCalculate event is fired. –  Ellesa Jun 15 '12 at 9:36
2  
the workaround for that is =CheckFilters(A3:E3) & &LEFT(SUBTOTAL(9,A5:A200),0) really, any function that makes it re-evaluate on a spreadsheet change –  Sean Cheshire Jun 21 '12 at 17:42
    
@Sean - This is great. I tested another solution but performance was slower: If I insert 'Application.Volatile' and the start of my function as per here: stackoverflow.com/questions/11045/… then it reevaluates correctly without the extra part in the formula. Performance suffers because the excel engine doesnt seem smart enough to only re-evaluate the UDF for the range it is defined over. Your solution only runs when the filter is changed which is ideal. –  Alex Jun 22 '12 at 23:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.