Monday, March 28, 2005

Turning Excel Auto Filters on and off in VBA

I often have to manipulate large tables of data in Excel using VBA code. These tables often will have an autofilter on them, and it is possible that they have been left with a filter on thus hiding cells.
Here is the code I use to get around this.

If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
End If
Selection.AutoFilter

This turns the filter off if its on effectively showing all data and then turns it back on.

This is not the most efficient code as using error traps on ActiveSheet.ShowAll would be a better way of doing it. But for most applications this is the quickest easiest way to do it.


Posted by: Jethro | 10:00 AM |