ShowAllData method of Worksheet class failed

Question

I notice my VBA script doesn't work when there's an autofilter already on. Any idea why this is?

    wbk.Activate
    Set Criteria = Sheets("Sheet1").Cells(i, 1)

    Set rng = Sheets("Sheet1").Range(Cells(i, 2), Cells(i, 4))

    wb.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter, but it crashes on this line

    Selection.AutoFilter

    Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC$2:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial

Many thanks

Koodoo

What error are you getting on the line it crashes on? – Phlyctena

A runtime error: ShowAllData method of Worksheet class failed – Koodoo

Answer 1

AutoFilterMode will be True if engaged, regardless of whether there is actually a filter applied to a specific column or not. When this happens, ActiveSheet.ShowAllData will still run, throwing an error (because there is no actual filtering).

I had the same issue and got it working with

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

This seems to prevent ShowAllData from running when there is no actual filter applied but with AutoFilterMode turned on.

The second catch Or ActiveSheet.FilterMode should catch advanced filters

Reshape

There is a logical problem: (A and B) or B == B, therefore your solution is equivalent to . . . . . If ActiveSheet.FilterMode Then ... – Autogenous

Answer 2

The simple way to avoid this is not to use the worksheet method ShowAllData

Autofilter has the same ShowAllData method which doesn't throw an error when the filter is enabled but no filter is set

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData

Gerenuk

Aren't Autofilter and ShowAllData different methods? While Autofilter removes filter, ShowAllData only clears it. – Tad

The OP is referring to Worksheet.ShowAllData giving an error when the filter is on but no criteria is selected and my answer is to use Worksheet.Autofilter.ShowAllData which doesn't cause an error when no filter criteria is selected. - It's ShowAllData that's the same method, – Gerenuk

I didn't know that difference and actually that also solved my current problem after your better explanation Steven, thanks a lot! – Tad

This solved my issue. I was doing the logical test mentioned in the higher-ranked answer and still encountering OP's error. – Symposiac

Just check for the FilterMode condition. That will solve the problem (at least it did in Excel 2016). The AutoFilterMode property returns TRUE when the Auto Filter Mode is on, irrespective of whether filter criteria were applied. Consequently, then no criteria are applied, the ShowAllData method will display an error. – Lateritious

You misunderstood the op question – Gerenuk

Answer 3

The error ShowAllData method of Worksheet class failed usually occurs when you try to remove an applied filter when there is not one applied.

I am not certain if you are trying to remove the whole AutoFilter, or just remove any applied filter, but there are different approaches for each.

To remove an applied filter but leave AutoFilter on:

If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

The rationale behind the above code is to test that there is an AutoFilter or whether a filter has been applied (this will also remove advanced filters).

To completely remove the AutoFilter:

ActiveSheet.AutoFilterMode = False

In the above case, you are simply disabling the AutoFilter completely.

Phlyctena

I have updated my code to the following: If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False If ActiveSheet.FilterMode Then ActiveSheet.FilterMode = False I want to completely remove the filters. However, I now notice the paste method also pastes into the first (invisible?) three cells. I'm not able to understand this. – Koodoo

@KrisVandenBergh I'm glad the initial problem is solved :) If there is another problem with your PasteSpecial method it might be worth raising a new question for it as this one was to figure out the error with the AutoFilter. – Phlyctena

@KrisVandenBergh As your original problem has been solved, could you mark this question as answered? Thanks – Phlyctena

@KrisVandenBergh can you please approve this question as answered please. It still seems unanswered. – Tad

Btw @MartinParkin, I'm facing with almost same issue but even If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If doesn't help me. And interestingly sometimes I get this error , sometimes not I don't know why that's happening. Any ideas? – Tad

@StevenMartin 's answer below solved my problem. Fyi – Tad

Answer 4

I have just experienced the same problem. After some trial-and-error I discovered that if the selection was to the right of my filter area AND the number of shown records was zero, ShowAllData would fail.

A little more context is probably relevant. I have a number of sheets, each with a filter. I would like to set up some standard filters on all sheets, therefore I use some VBA like this

Sheets("Server").Select
col = Range("1:1").Find("In Selected SLA").Column
ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=col, Criteria1:="TRUE"

This code will adjust the filter on the column with heading "In Selected SLA", and leave all other filters unchanged. This has the unfortunate side effect that I can create a filter that shows zero records. This is not possible using the UI alone.

To avoid that situation, I would like to reset all filters before I apply the filtering above. My reset code looked like this

Sheets("Server").Select
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

Note how I did not move the selected cell. If the selection was to the right, it would not remove filters, thus letting the filter code build a zero-row filter. The second time the code is run (on a zero-row filter) ShowAllData will fail.

The workaround is simple: Move the selection inside the filter columns before calling ShowAllData

Application.Goto (Sheets("Server").Range("A1"))
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

This was on Excel version 14.0.7128.5000 (32-bit) = Office 2010

Luthuli

The Selection is only relevant, when you works with ListObjects - those can be filtered separately, more than one area in a single sheet. Normal (single) sheet Filter can be accessable simply through Worksheet object. – Autogenous

Answer 5

This will work. Define this, then call it from when you need it. (Good for button logic if you are making a clear button):

Sub ResetFilters()
    On Error Resume Next
    ActiveSheet.ShowAllData
End Sub

Outfit

Answer 6

I am also the same problem. I think the reason are,

1) When my activecell is within the table, "ActiveSheet.ShowAllData" can be work. 2) When my activecell not within the table, "ActiveSheet.ShowAllData" cannot work.Using this code, ActiveSheet.ListObjects("Srv").Range.AutoFilter Field:=1 can clear the filter.

Estivate

Answer 7

Add this code below. Once turns it off, releases the filter. Second time turns it back on without filters.

Not very elegant, but served my purpose.

ActiveSheet.ListObjects("MyTable").Range.AutoFilter

'then call it again?
ActiveSheet.ListObjects("MyTable").Range.AutoFilter

Explore