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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  quot MyTable quot   Range AutoFilter   then call it again  ActiveSheet ListObjects  quot MyTable quot   Range AutoFilter

User · Answer

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

[excel] ShowAllData method of Worksheet class failed

Examples related to excel

Examples related to vba

Examples related to autofilter