Excel VBA - AdvancedFilter

Question

I am trying to filter a range dynamically in VBA and the VBA I am using is not working but I cannot see a logical reason as to why. To explain, I have a range of data in a sheet entitled "Full Stock Report" the size of which will change but I've set it statically in this example... And I'm trying to filter it by a list of criteria held in a range on a sheet initiated "Spitfire Aval Locations", again this is also dynamic but I've set as static again in this example. This sounds simple to me but the below line of code applies a filter but with no results (I have checked I know there are lots that should appear from this filter).

My second question is related, how does this VBA statement dictate which column in the range is being filtered ? (I fear this may be my issue ....)

Sheets("Full Stock Report").Range("A1:F20623").AdvancedFilter Action:=xlFilterInPlace, 
      CriteriaRange:=Sheets("Spitfire Aval Locations").range("A2:A228"), Unique:=False

Answer 1

Think I've solved this ... essentially AdvancedFilter requires the criteria to be the same format and same column titles as your data set. Not hugeley helpful to me, but I can bodge it to work.

I also have a hunch that AutoFilter with specified criteria might be a better option...

Answer 2

The column to filter on is the first .Range that you call .AdvancedFilter on. The code you posted filters columns A through F. If you wanted to only filter based on values in column A, it would look more like this:

Sheets("Full Stock Report").Range("A1:A20623").AdvancedFilter _
       Action:=xlFilterInPlace, _
       CriteriaRange:=Sheets("Spitfire Aval Locations").Range("A2:A228"), _
       Unique:=False