Vba errors 400 and 1004

**headexperiment** · 06-13-2011, 06:04 AM

Hi,

Grateful if anyone can help.

I have a worksheet called "Front" which contains a bunch of named ranges and a couple of macro buttons.

Behind it is a sheet called "List" which contains an autofiltered table.

One of the macros on "Front" is to reset the options and clear the autofilter on "List". Code below. It sometimes works. Sometimes I get an error that just says "400". Sometimes, if I run it from the VBA window, I get a runtime 1004 error.

What am I doing wrong?

Thanks,

HE.

Sub showall()

Worksheets("Front").Range("Option_att_flag").Value = "Any"
Worksheets("Front").Range("option_gender").Value = "Any"
Worksheets("Front").Range("option_cic").Value = "Any"
Worksheets("Front").Range("option_SenLevel").Value = "Any"
Worksheets("List").ShowAllData

End Sub

**mohd9876** · 06-13-2011, 06:32 AM

try this:

Worksheets("List").AutoFilter.ShowAllData

because the line that you already have generates an error if there is no filter in the worksheet

**Colin Legg** · 06-13-2011, 06:39 AM

Hi,

If you try to show all data when either

(1) There isn't an autofilter object or
(2) The autofilter object isn't actively filtering anything

Then you'll get a runtime error. Other things may interfere too - such as worksheet protection.

You are using XL 2003 so I would do it like this:

Sub ShowAll()

    Worksheets("Front").Range("Option_att_flag").Value = "Any"
    Worksheets("Front").Range("option_gender").Value = "Any"
    Worksheets("Front").Range("option_cic").Value = "Any"
    Worksheets("Front").Range("option_SenLevel").Value = "Any"
    
    If Worksheets("List").AutoFilterMode Then
        If Worksheets("List").FilterMode Then
            Worksheets("List").ShowAllData
        End If
    End If
    
End Sub

@ mohd9876
Unfortunately, in XL 2003 the Autofilter object does not have a ShowAllData method, so headexperiment won't be able to check your suggestion.