VBA Unfilter Range

Question

I am using this code:

Sheets("Sheet1").AutofilterMode = False 

to filter data on an Excel worksheet using VBA (you need to clear all filters). This does not seem to always work, is there a better way?

Thanks!

In case this helps, this table is connected to the Sql server (data → from other sources → from the Sql server ...) and has a color design layout (for a specific table).

Answer 1

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

Answer 2

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

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

Then call the function from your main unit:

 Sub Main() ShowAllData End Sub