AutoFilter

[ketchupguy37]

hey guys,

I was wondernig if someone could assist me with an issue I am having with the auto filter options in excel. For some reason it is only sorting up to row 140. Does anyone have any idea why it would be doing this?

[ncmay]

If there is any empty cells it might cause problems.

[ketchupguy37]

The ones that aren't being filtered do have stuff in them but it isn't sorting accordingly.

[kellyfspringer]

What do you mean by sorting? I have to agree with ncmay, that if there are any blank rows in your data, the auto filter will not work past them. When ever I have this, I sort the data first and delete all the blank rows, then I can use the autofilter without problems.

[paulean]

Just go in to Advance Filter which opiton is next to Auto adn filtered it it will filtered the whole Sheet coloum of excell which u select.

[martindwilson]

that if there are any blank rows in your data, the auto filter will not work past them

thats not the case,mine works fine

[kellyfspringer]

thats not the case,mine works fine

Even when the ENTIRE row is blank? I come across this problem all the time. Does it just seem to be the blanks that are the problem?

----
and Excellent tip, Romper, about highlighting the entire data, rather than just the top row!!

[broro183]

hi all,

As Kelly questioned "what do you mean by sorting?", "Sorting" & "Filtering" are two separate & different functionalities in Excel.

Assuming you mean that the rows aren't being filtered below row 140...

If a single cell (or the header row) is selected then the range of the autofilter is "guessed" by Excel. I think it uses the "currentregion" (ie the area around the current selection that is bordered by empty cells) to try & identify the appropriate range. You can get the address of the range used by the autofilter through the VBE's Immediate pane:
choose the sheet that the autofilter is on.
in Excel, press [alt + F11] to open the VBE.
press [ctrl + g] to open the Immediate pane in the VBE
type

?activesheet.AutoFilter.Range.Address

& press [enter] to give the answer.

To fix your current problem & to be certain that all the necessary rows are included, I suggest removing your current autofilter, selecting the entire range* (not just the headers) before re-applying the Autofilter. Now any new data rows that are added directly below the initial range become included in the autofilter range, however new data rows aren't added to the autofilter range if a blank row is between the existing range & the new data.

*Any blank rows in the initial range will now be included in the range acted on by the autofilter.

hth
Rob

[romperstomper]

Rob,
Just FYI, from 2003 onward, there are sort options built into the Autofilter dropdowns.

[broro183]

Good point Romper & apologies to KetchupGuy, I am playing in 2002 at the moment & had forgotten about that option in 2003.

Rob

[przemke]

Hi
I have similar problem: auto filter does not work past row60, and blanks are not the problem (before 60 they are not displayed and after anything is displayed)
Attached is the file with confidential info deleted (I sort those for "yes")
I use excel 2003

[royUK]

przemkeYour post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[royUK]

If an entire row or column within the data is blank then that row or column will be the limit of the filter

[przemke]

Thank you RoyUK for the remark. However I have to say I am aware of that forum rule, but I noticed it's like exactly the same problem that discussion was about.
OK and thanks for actually solving my problem ! :D

[martindwilson]

If an entire row or column within the data is blank then that row or column will be the limit of the filter

is that a 2003 thing ? its certainly not true in 97 or 2007 or are we at cross purposes here?

[romperstomper]

It should depend on what you select to start with. If you select one cell, then you'll get the current region (which will stop at a blank row); if you select all the data first, you'll be fine.