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

In this case the macro recorder might help. Record, do what needs to be done, stop recording and adjust the macro. — Moritz Schmitz v. Hülst
– Moritz Schmitz v. Hülst, Commented Apr 18, 2015 at 16:07
I tried that, but unfortunatley, same result, hence the predicament — DJDave022002
– DJDave022002, Commented Apr 18, 2015 at 17:05

DJDave022002 · Accepted Answer · 2015-04-22 10:24:57Z

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...

answered Apr 22, 2015 at 10:24

DJDave022002

231 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MrZH6 Over a year ago

Good point Dave. I've also struggeled with this method and this is the solution!

Comintern · Accepted Answer · 2015-04-18 14:32:39Z

0

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

answered Apr 18, 2015 at 14:32

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

4 Comments

DJDave022002 Over a year ago

Excellent point, thank you, and i was expecting that - but how does it know the full range to filter, even though its filtering against one column ?

Comintern Over a year ago

@DJDave022002 - I'm not sure I understand the question - filtering hides entire rows, so you only need to include the columns that you want filtered against your criteria.

DJDave022002 Over a year ago

Another good point, appolagies i didn't consider the fact that it doesnt filter a specific range, stupid schoolboy mistake, I was looking at the defined range rather than considering it wikll hide entire rows regadless of defined range. Sorry...

DJDave022002 Over a year ago

Sorry -- That does make sense, however it yields no result when tried, even though i know the main list to contain many entries that exist in the criteria list.

Collectives™ on Stack Overflow

Excel VBA - AdvancedFilter

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related