[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 792
  • Last Modified:

Excel filter works only partially


One user here where I work has got a very strange problem with Excel in one particular sheet of one Excel: when she filters on a column which has only pure text in it and she wants to find the rows where the cells in that column are blanks, then the filter works for about the first 2/3 of all rows, and then the last 1/3 of the rows is ALWAYS shown regardless of the filter in that column is set to ! And strange is that the properly filtered rows have their row numbers correctly shown in blue font (which is the colour of filtered rows), and the last 1/3 of rows have their row numbers in black font, as has every row when no filter is active. It's as if the last third of rows have a special "status" which takes them out of the "filterable" rows. There are only about 130 rows, that's a very small sheet.

When we copied the whole sheet and pasted the values in a new sheet, the same filter on that column worked !

Anybody seen that one before ? Ideas about what it could be due to ?

Thanks for hints
1 Solution
Rob HensonIT & Database AssistantCommented:
Try removing the filter altogether, not just disable but remove, and re-applying. It may be that the last rows haven't been included in the filter.

Rob H
Glenn RayExcel VBA DeveloperCommented:
This is happening because there is/are blank rows in the data table and the auto-filter is limiting the range to the first blank row.  That means any data after that is being excluded from the filter.

Rob is correct:  you need to remove Data Filters completely.  Then, highlight the entire range of data by pressing [Ctrl]+[Home], then press [Ctrl]+[Shift]+[End] to select all used cells.  THEN, turn on data filters again.  It should work.

Ideally, you should have no blank rows at all in a data table and I suggest that you remove them.

bthouinAuthor Commented:
Hi Rob, hi Glenn

Thanks for your speedy answers.

Yes Rob, in the mean time that's what the users had the idea of doing and the filter ineed worked propoerly after remove and reapply.

@Glenn: I didn't check, but I don't think there were/are any blank rows, just blank cells in the column being filtered

Also, there were merged cells on the first row (header texts), I asked them to unmerge them because I remembered that merged cells can be quite a problem in many situations. But what we didn't do was to remove the filters and reapply them then. They did that on their own an hour after I'd been there and that's when it started to work, but I had already made my post..

Thnaks again.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now