Advanced Filter works manually but not in VBA?

**rob0r** · 11-17-2009, 06:00 AM

Hello,

I have setup an advanced filter that is extremely temperamental, the problem seems to be that it ignores the criteria range when the code is read in VBA. The advanced filter code is stored in Module1 and the code is called by a command button on a worksheet. I can get the filter to recognise the criteria if I go through the advanced filter tool on the toolbar OR if I step through the exact same vba code using F8. However it doesn’t recognise the criteria and the advanced filter does not work properly if I call the code from the command button on the worksheet. I’ve read that this has happened before but I’m stumped as how to sort it out. I’m using excel 2007 in compability mode with 2003, and the exact same thing happens in 2003.

Has anyone got any ideas why this might be the case?

**Richard Buttrey** · 11-17-2009, 06:07 AM

Hi,

Difficult to be certain without seeing the workbook in question, but I'd hazard a guess that you are not fully referencing the criteria range. This is often the cause of a macro working when you step through it but not otherwise since the tendency when stepping through is to keep jumping back to Excel to see what's happening on the active sheet.

Try referencing your criteriarange with a fill workbook/sheet/range reference.

HTH

**bullkater** · 11-28-2009, 05:13 PM

Hi, I'm a newbie to the forum, but not to Excel.

I seem to have the same problem as rob0r. I record a macro for an advanced filter with copy to another location. While recording the filter works, but as soon as I run the recorded macro, the only things that happen is the creation of two named fields ("Criteria" and "Extract") and the header line of the extracted data at the new location is written.

I think it's not due to the reference as suggested by Richard Buttrey (because I did the same macro recording with a small data set (12 lines x 3 rows) and here the execution works as wanted

), but probably to the size of the original data set (43'600 lines x 27 rows). Could that be reason?

If needed I can send the file. Any help is very much appreciated. Thank you in advance.

**Richard Buttrey** · 11-28-2009, 05:25 PM

Hi,

Welcome to the forum.

You would be better advised to start a new thread of your own - please see the forum rules, in particular rule 2.

This is for your own benefit and you're likely to receive a larger response since more people look at original threads than look at already established threads.

By all means also post a link to this thread.

Kind regards