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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.