Filter Data Based on referencing a range of cells that change

[davidhall]

I am have a list of data on Sheet 1 which has nearly 40k lines of data. The titles of the data are in columns A through F. Column A contains numerous Shirt Styles which is the filter criteria for the data set.

On Sheet 2, column B, cells B10 through B60 contain shirt styles which should be used as the filter criteria. The data in cells B10 through B60 might not be entirely populated with 50 styles ( so it could have blank cells), but this will always be the master list of shirts styles needed. There will be times when styles are removed and styles might be added to the list so the filtering function needs to be dynamic in that sense.

The data on Sheet 1 should be autofiltered by the Shirt Style and the selection for the filter needs to be based on the data on Sheet 2 B10 through B60.

Any suggestions?

 

[GlennUK]

Are the blank cells in the filter criteria only at the end of the populated styles there?

 

[davidhall]

Yes. On Sheet 2, range B10:B60, the beginning style number will always start on B10, the next on B11, the next on B12. So, if there were only 3 style numbers then range B13:B60 would be blank. There will never be a time where B10 will contain data, B11 nothing, and B12 containing data.

 

[GlennUK]

In that case use a define name to calculate the filter criteria ( you must have the list heading from column A of the data sheet in cell B9 of sheet2 ).

Create a defined name of FiltCrit with a formula in the RefersTo of:
=OFFSET(Sheet2!$B$9,0,0,COUNTA(Sheet2!$B:$B),1)

Now you can filter your main list: Data/Filter/Advanced/Filter In Place, Criteria Range of FiltCrit

... that's it.

 

[davidhall]

Glenn,

How could I write that in VBA?

 

[davidhall]

Figured it out. Thanks for your help.

 

[GlennUK]

It's my pleasure