REMOVING COLUMN FILTERS VIA VB CODE

[clawdogs]

Anyone know how to remove column filters via VB code? I'm trying to
code for an automated macro.. Thanks in advance..

 

[Clif McIrvin]

Anyone know how to remove column filters via VB code? I'm trying to
code for an automated macro.. Thanks in advance..

Have you tried using the macro recorder while manually making the
changes you are interested in and then examining the code?

 

[clawdogs]

Have you tried using the macro recorder while manually making the
changes you are interested in and then examining the code?

yes, that won't work if filters are already off. it will then include
filters

 

[GS]

clawdogs pretended :

Anyone know how to remove column filters via VB code? I'm trying to
code for an automated macro.. Thanks in advance..

Google FilterMode property in online help (F1)!

 

[MerseyBeat]

Have you tried using the macro recorder while manually making the
changes you are interested in and then examining the code?

yes, that won't work if filters are already off. it will then include
filters

The obvious recommendation would then be.... do it a second time after it
includes filters and you will have the code to remove them ???

 

[GS]

MerseyBeat explained on 9/23/2011 :

yes, that won't work if filters are already off. it will then include
filters

The obvious recommendation would then be.... do it a second time after it
includes filters and you will have the code to remove them ???

With ActiveSheet
If .FilterMode Then .AutoFilter '//turn it off
Wnd With 'ActiveSheet

 

[GS]

GS made a typo...:

With ActiveSheet
If .FilterMode Then .AutoFilter '//turn it off

End With 'ActiveSheet

 

[GS]

There's a bit more to it that I didn't consider... (my bad!)

AutoFilter
Must be used with a range of contiguous cells
This toggles the filter dropdowns

FilterMode
True if rows are filtered;
False if rows are not filtered (even when the dropdowns exist)

AutoFilterMode
True if dropdowns exist;
False if no dropdowns exist

So.., my reply should have been as follows...

With ActiveSheet
If .AutoFilterMode Then .UsedRange.AutoFilter '//turn it off
End With 'ActiveSheet

If rows are filtered:
ActiveSheet.FilterMode is True
ActiveSheet.AutoFilterMode is True

If rows are not filtered:
Dropdowns exist:
ActiveSheet.FilterMode is False
ActiveSheet.AutoFilterMode is True

Dropdowns do not exist:
ActiveSheet.FilterMode is False
ActiveSheet.AutoFilterMode is False

 

[Dave Peterson]

Saved from a previous post:

If turn the autofilter off means remove all the arrows and show all the data:

dim wks as worksheet
set wks = worksheets("Somesheetname")
wks.autofiltermode = false


If turn off just means that you show all the data and keep the arrows:

dim wks as worksheet
set wks = worksheets("Somesheetname")
with wks
'show all the data
If .FilterMode Then
.ShowAllData
End If
end with

 

[clawdogs]

Saved from a previous post:

If turn the autofilter off means remove all the arrows and show all the data:

dim wks as worksheet
set wks = worksheets("Somesheetname")
wks.autofiltermode = false

If turn off just means that you show all the data and keep the arrows:

dim wks as worksheet
set wks = worksheets("Somesheetname")
with wks
    'show all the data
    If .FilterMode Then
       .ShowAllData
    End If
end with

Guys, thanks a bunch!