Use Data Filter on Hidden Data? (E2003 SP3)

[steveh]

Good afternoon

I have 1700 rows in 4 columns of Data on which I use an autofilter, I would however like to have all of the source data hidden until the user selects what they want to view. depending on what column of data they choose they will see- Columns A, B and C only return a unique result, but if column D is selcted there could be anything from 1 - 100 results.

I thought that by moving the data to the bottom of the page (A60000:D61690) I could use the auto filter (I have never used it before so it was a shot in the dark) to nominate where to look for the data but it did not even give me the option. Can I do this or should I look for other methods?

[HansV]

I wouldn't move the data to the bottom of the sheet, that's very inefficient.
You could select Custom... from one of the filter dropdown arrows, and specify a non-existent value. When you apply the filter, all data rows will be hidden.

[Don Wells]

...and specify a non-existent value. ...

Didn't you mean "..and specify Blanks. ..."?

[HansV]

No - the data could contain blanks in some columns but not in others, so selecting Blanks might not hide all data rows.

[Don Wells]

Of course! I forgot about the "Custom" entries.

Thank you Hans.

[steveh]

I wouldn't move the data to the bottom of the sheet, that's very inefficient.
You could select Custom... from one of the filter dropdown arrows, and specify a non-existent value. When you apply the filter, all data rows will be hidden.

Thanks Hans

Perfect as usual