Autofilter help

[scottc786]

Hi Guys - Does anyone know how i can test to see if the autofilter, while enabled, is not currently filtering?

Tks

 

[jamieleeuk]

I'm not entirely sure what you mean, so forgive me if this explanation is of no use to you.

You'll know when you are filterin data if you look at the row numbers to the left of the xl screen. They change to a blue colour and they hide certain rows which doesn't contain the active filter.
You can then clik on the filter arrow and select "All" to show all the data in that criteria column.

Again, apologies if this isn't the answer you were looking for, I just don't fully understand your question.

Regards

 

[scottc786]

sorry..... to make myself a touch clearer.... In vb how can i test, if the autofilter is on, but everything is set to 'All' (i.e. is currently having no effect)

 

[schielrn]

This will tell you if it is filtering or not:

Sub testFilter()
For Each f In ActiveSheet.AutoFilter.Filters
    If f.On = True Then
        MsgBox "Filter is applied in one of your columns"
        Exit Sub
    End If
Next f
MsgBox "Filter is not applied"
End Sub

Hope that helps.

 

[scottc786]

Nice1... tks

 

[schielrn]

You could also, if you really wanted to, have it return columns that are currently filtered and other things, but not sure what you want as an end result?

 

[scottc786]

How would i do that?

I have a box that need to change value depending on if the filter is set or not in the 3rd colum.... it can be active on the others though and it not matter.

Tks

 

[schielrn]

Maybe have a look into this and modify to your needs:

Sub testFilter()
For Each f In ActiveSheet.AutoFilter.Filters
    If f.On = True Then
        MsgBox "Column " & ActiveSheet.AutoFilter.Range.Resize(1, 1).Offset(, counter).Address(0, 0) & " is filtered on"
        Exit Sub
    End If
    counter = counter + 1
Next f
MsgBox "Filter is not applied"
End Sub

This only checks for the first instance of something being filtered and not all instances. Remove the exit sub if you want all instances and you will probably need to change how the last message box is displayed.

Hope that helps.

 

[scottc786]

Could this be adpated to return the values that the filter is currently set to?

 

[schielrn]

I assume, by that you meant he criteria that are being applied to the filter? If so have a look at this, but I haven't found anything for 2007 yet, to have this work:

http://www.mrexcel.com/forum/showthread.php?t=371059&highlight=return+filter+criteria

Hope that helps.