Need macro to turn off AutoFilters in a table (repost)

[Jeremy]

I have data in an Excel 2007 table called "table2". I have a macro that
turns on the AutoFilters drop-down arrows in the table. It works fine in
Excel 2003 and 2007.

I have another macro that in Excel 2003 works fine to turn off the
AutoFilters in the table, but it doesn't work for Excel 2007. The macro that
works for Excel 2003 is:

Sub RemoveAutoFilters()
If ActiveSheet.AutoFilterMode = True Then
ActiveSheet.AutoFilterMode = False
End If
End Sub

What am I doing wrong that it won't work in Excel 2007? I have Bing/Googled
it but can't find the answer. Thank you!

 

[Barb Reinhardt]

To remove the filters, you can do this

ActiveSheet.ShowAllData

You can still reapply the filters later.

 

[OssieMac]

Hi Jeremy,

You need to reference the table in the worksheet not just the worksheet.
The "If/End If " is not required if you simply want to ensure AutoFilter is
Off.

Example 1:
Sub TablesAndAutoFilter()

Dim ws As Worksheet
Dim objList As ListObject

Set ws = ActiveWorkbook.Worksheets("Sheet1")

Set objList = ws.ListObjects("Table2")

If objList.ShowAutoFilter Then 'Omitt if simply turning off
objList.ShowAutoFilter = False
End If 'Omitt if simply turning off

End Sub


Example 2:
Without assigning to variables and simply ensuring that autofilter is off.

ActiveWorkbook.Worksheets("Sheet1") _
.ListObjects("Table2") _
.ShowAutoFilter = False

 

[Les Gombart]

Hi Jeremy...

assuming your table starts at range = A1 a VERY simple script is..

Sub RemoveAutoFilters()

Range("A1").Select
Selection.AutoFilter

end sub

This code merely "switches" autofilter on if it is off or off if it is on...
If mutliple tables, then merely use the same script pointing to the various
ranges

 

[Ron de Bruin]

ActiveSheet.ShowAllData is working in a Table

See also
http://msdn.microsoft.com/en-us/library/dd795215(office.11).aspx

 

[Jeremy]

Many thanks OssieMac. It works perfectly.