VBA Unfilter range

Question

I am using this code:

Sheets("Sheet1").AutofilterMode = False

to unfilter the data in an Excel sheet using VBA (the point is to clear all the filters). This doesn't seem to always work, is there a better way?

Thank you!

In case it helps, this table is linked from Sql Server (Data --> From other sources --> From Sql Server...) and it has a coloured design layout (table specific).

Answer 1

Use Worksheets("Sheet1").ShowAllData instead. See http://msdn.microsoft.com/en-us/library/office/bb178108%28v=office.12%29.aspx.

Answer 2

The ShowAllData will work only if your sheet has a filter, otherwise it will break. I found that you can create a function from this with On Error Resume Next and it should work in all cases:

Sub ShowAllData()
On Error Resume Next
Worksheets("Sheet1").ShowAllData

End Sub

Then call function from your main sub:

Sub Main()

ShowAllData

End Sub