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.

Please Login or Register  to view this content.

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

try this:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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