VBA criteria to reference cell in workbook

[tony7262]

Hi,

I have the below code and I would like to have the filter that current looks for the string 'Alpha Region' to be dynamic and refer to cell B1 on the tab RegionalAnalysis. Currently it is just referring to a text string - is there anyway to make this dynamic?

Any help greatly appreciated

[vcoolio]

Hello Tony,

You could just change the Criteria1 as follows:-

Firstly declare a variable such as:-

Please Login or Register  to view this content.

then give the variable a value:-

Please Login or Register  to view this content.

then filter on Crit1

Please Login or Register  to view this content.

You should then be able to filter on whatever value that you place in B1 (from a drop down perhaps?).

I hope that this helps.

Cheerio,
vcoolio.

[tony7262]

Hi vcoolio,

Thank you - for some reason I had to take the check off if there was any data displayed after Autofiltering as it was only working for some regions but not others - but I can look at that later.

Thanks for your help!

[vcoolio]

You're welcome Tony. I'm glad to have been able to assist.

Generally, using the Autofilter within a "With" statement, should negate the need for a check. A couple of examples follow:-

Please Login or Register  to view this content.

...or, if there is at least one completely empty row directly above the dataset (i.e. Row 4), then this should work as well:

Please Login or Register  to view this content.

Thanks for the rep!

Cheerio,
vcoolio.