REMOVING COLUMN FILTERS VIA VB CODE

  • Thread starter Thread starter clawdogs
  • Start date Start date
C

clawdogs

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


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

yes, that won't work if filters are already off. it will then include
filters
 
clawdogs pretended :
Anyone know how to remove column filters via VB code? I'm trying to
code for an automated macro.. Thanks in advance..
Click to expand...

Google FilterMode property in online help (F1)!
 
Have you tried using the macro recorder while manually making the
changes you are interested in and then examining the code?
Click to expand...

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 ???
 
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 ???
Click to expand...

With ActiveSheet
If .FilterMode Then .AutoFilter '//turn it off
Wnd With 'ActiveSheet
 
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
 
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
 
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
Click to expand...
Guys, thanks a bunch!
 
Back
Top