ShoAllData Error using autofilter

December 19, 2016 at 11:24:54
Specs: Windows 7
Can anyone help me figure out why all of a sudden I started getting this error?

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

the debug fails at ActiveSheet.ShowAllData

I have made some minor changes and have retraced my steps but can't seem to find the reason this is happening and I cannot figure out a fix.

Sub Pipeline_ShowNetRegs()
'shows all net registrations including loans that have closed in the current month
ActiveSheet.Unprotect
Dim ddate As String
Dim r As Range
ddate = Month(Date)
Set r = ActiveSheet.Range("$a$6:$EZ$1000")
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    Selection.AutoFilter
    ActiveSheet.Range("$A$6:$EZ$1000").AutoFilter Field:=34, Criteria1:="<>Pre-Approval"
    ActiveSheet.Range("$A$6:$EZ$1000").AutoFilter Field:=156, Criteria1:=""
    ActiveSheet.Range("$A$6:$EZ$1000").AutoFilter Field:=6, Criteria1:="<>"
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
    Range("$A$6:$EZ$1000").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    ActiveWindow.ScrollColumn = 11
Worksheets("Pipeline").Shapes("Drop Down 261").ControlFormat.Value = 1
Worksheets("Pipeline").Shapes("Drop Down 264").ControlFormat.Value = 1
Worksheets("Pipeline").CheckBoxPreApproval.Value = False
Worksheets("Pipeline").CheckBoxProjected.Value = False
Worksheets("Pipeline").CheckBoxCRA.Value = False
Worksheets("Pipeline").CheckBoxClosed.Value = False
Range("E5").Value = "Current Filter = Net Regs"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

message edited by mecerrato


See More: ShoAllData Error using autofilter

Report •

#1
December 19, 2016 at 12:32:13
I obviously don't have your data set or workbook, so I simply filled A6:EZ1000 with a bunch of data.

I ran the code once and it skipped the ActiveSheet.ShowAllData instruction since no filters were applied.

Once the filters were applied by the code I single stepped through the code and it executed the ActiveSheet.ShowAllData instruction with no error.

I can't help with your problem if I can't replicate it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 19, 2016 at 14:09:41
Thanks DerbyDad03 but all of a sudden it started working, this is so odd. I think it may have something to do with my excel setup. This is a sheet I have been working on for the whole year and have incrementally added functionality. Not sure what triggered the error but just like it mysteriously showed up it mysteriously disappeared.

Report •
Related Solutions


Ask Question