Need Excel macro to turn off AutoFormat in a table

[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!

 

[Jeremy]

Sorry the title is wrong - I meant Excel macro to turm of AutoFilter in a
table. I will repost.

 

[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 'Omitted if simply turning off
objList.ShowAutoFilter = False
End If 'Omitted 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