Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.

Run-time error '1004': ShowAllData method of Worksheet class failed

Latest post Tue, May 17 2011 12:46 PM by Gizmo. 1 replies.
  • Tue, May 10 2011 1:50 PM

    • Giobabie
    • Not Ranked
    • Joined on Tue, May 10 2011
    • Posts 1
    • Points 21

    Run-time error '1004': ShowAllData method of Worksheet class failed

    I'd appreciate any help.  I made a slight alteration (added a column "M") to a string of macros but I keep getting on error on the part illustrated below.  Any thoughts?

    'Next File in the Directory
        MyFiles = Dir
        Loop
        
        Selection.AutoFilter Field:=1, Criteria1:="Date"
        Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Delete Shift:=xlUp
        ActiveSheet.ShowAllData
        Range("A2").Select

    Filed under:
    • Post Points: 21
  • Tue, May 17 2011 12:46 PM In reply to

    • Gizmo
    • Top 25 Contributor
    • Joined on Mon, Jan 21 2008
    • Hampshire, England
    • Posts 107
    • Points 1,713

    Re: Run-time error '1004': ShowAllData method of Worksheet class failed

    The .ShowAllData essentially de-selects an autofilter without removing it from the sheet.

    This error means that it's trying to carry this out when a filter is not actually in use on the activesheet.

    If I need to do this I just put it in a 'Resume Next' error trap.

    'clear any filters
        On Error Resume Next
            ActiveSheet.ShowAllData
        On Error GoTo 0

     

    HTH

    Giz

    Motto? "There's always a better way" - all you have to do is find it!

    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.