Filtering and Finding Hidden Rows (2003)

Topic

New Reply

I have been asked about this, and I don’t know the answer, so I’m asking the lounge members:

A worksheet has several hidden rows. When the auto filter is turned on, and a search is made for criteria that is in the hidden rows, the hidden rows appear in the filtered result.

For example, let’s say that I have a list of 100 employees in a worksheet. The columns include the Department. Now, let’s say that I have two employees in the sales department whose rows I have hidden. Now I filter the worksheet on the Department column, and I look for all employees in the Sales department. When I click on OK, the two hidden employees show up in the filtered result.

Is there a way to prevent the hidden rows from appearing in a filtered result?

Reply | Quote

Replies

No, that is not possible. Filtering also works by hiding and unhiding rows, so by its very nature, the filter dropdown list includes hidden items.

Reply | Quote

Bob,
Depending on your needs, there may be a way…
In a blank column of the worksheet, type an entry in the rows you wish to remain hidden (I named the column KeepHidden).
When you do your filtering (Like for Sales), also filter on KeepHidden and tell it to show only Blanks.
I attached a workbook sample.
Hope t helps.

Reply | Quote