Excel: Advanced Filter in Macro Problem?

Question

I am using an advanced filter and have recorded a macro to speed up the work, the problem is when I do it manually it works fine but, when I run a macro the advanced filter doesn't pull through any data.

The code for the macro is below.

' Regenerate_Schedule Macro

'

'

Range("A4").Select

Range("A4:P465").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("F1:G2"), Unique:=False

Range("A4").Select

End Sub

Any help would be great.

Paul.

Answer 1

A macro will only do exactly what you tell it to.  Two of the lines in your macro you don't even need.

Actually your macro is just one line.  The space then underscore represents a line continuation.

Range("A4:P465").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _

Range("F1:G2"), Unique:=False

Your macro is basically telling the computer:

Do an advanced filter on the range A4:P465.  It's telling it to filter that range in place instead of filtering the list and placing it in a new worksheet or something like that.  It's telling it to use the range F1:G2 as the range for filtering criteria.  And it won't bring back just the unique values because Unique is set to false.

The lines that state Range("A4").Select you don't need.

If you still have trouble, you might try something like:

Range("A4:P465").Select

Selection.AdvancedFilter Action:=...

Make sure your ranges are the correct ranges.