Excel autofilter stops filtering Solved/Closed

Question

Hello,

I am working with Excel 2003 professional and just yesterday after entering a bunch of records to my file, which has about 19,000 rows, I discovered that my auto-filter just stopped filtering completely. 

When I clicked the arrow, there is no value in the dropdown list. There are just All, Custom, and Top 10. But even these options, when I tried to click on them, do not work either.

I have removed and re-applied the filter, but the problem remained as same. I did google and try the manual calculation mode. That did not work for me. 

Even I tried to copy a dozen of records to a new worksheet and applied the auto-filter, but it showed nothing.

Please help me guys, I cannot survive without my auto-filter.

Thanks a million!
System Configuration: Windows XP Firefox 2.0.0.14

DI4BL0S · Accepted Answer

Hey, 

The following also solves the problem:

Instead of selecting the Row that you want to apply the autofilter on, select all the columns (A, B, C ...) and then apply the autofilter.

We've been solving this problem for many of our customers and so far it has always worked.

Thanks!

Collette · Answer

Lucy - thank you so much!  I had the same issue just happen and googled "excel autofilter won't work" and found your post.  Your solution worked.  I continue to wonder how I ever survived without the internet.  Thanks!

trebroN · Answer

Thank you Lucy!
I've an urgent work but without your solution I can't finish it.

roulette_21 · Answer

hi..

I also have problem in autofilter, I used excel 2003. I have tried the solution above but it still not working properly.

I am working on an excel file that have approximately 3000 rows and 30 collumns and using autofilter..

The autofilter not working properly for some collumn but working for other collumns. 

For example for collumn A I have 3000 rows with maybe around 1000 different items (some is duplicated with different input in other collumns), when I try to use the drop down list in collumn A, the list is not showing all the item available in collumn A (eg 'AAA'). 

But when I filter collumn B (eg 'BBB'), then I want to filter again using column A, the items 'AAA' appears in the drop down list.

The question is why the items 'AAA' not appear in the drop down list when I haven't filtered other collumns first? 

Please help, your reply is greatly appreciated.

Thanks

mTim · Answer

Lucy, thank you so much for posting this trick! It worked as a charm! :)

Marshall · Answer

Thanks Lucy, great help.  I can accross via a google search. cheers

Lucy · Answer

To: K8ster598

Sorry I could not help you, I'm a Windows girl not a Mac:) Try Mac forums may help you. anyway, hope that you already fixed it by the time this message posted.

Everyone

Thank you for all the thank you's ;) I think everyone gets help from the Internet all the time so don't worry.

If the solution does not work, pls post/ create a new thread. 

Moderator, pls close this discussion. Thank you.

Lori · Answer

I can not understand what you are saying about how to reset the filter.    Where do you type in the "sheet!_filter etc.)?    My filter mode works one minute and not the next.    How do you change to make it automatic.

NightPorter · Answer

I tried the GOTO thing, it selected what looked like the right area - although some columns did not have the non-working drop down arrows.  I did an Autofilter command and it added arrows on every column that did work.  When I did it again, it removed the working ones, leaving the non-working ones as they were before I did anything.  I redefined the name to be the entire area.  I can add and remove new drop down arrows as much as I want, but those three useless bits of grey picture are still there.  Any ideas?

David · Answer

Using Excel 2003, I am having a problem filtering in that the most of the rows do not show up in the filter list. I have determined that if the cell contains 255 characters or less it shows in the list, if it contains 256 or more characters it does not show up in the filter list.

VIC · Answer

For autofilter to work properly, make sure that your data range does not have any empty columns when filtering. 
If you do have empty columns, put a space or anything in the autofilter heading or body to allow it to form as part of the range so the empty colums are picked up.

The empty colums spilts the auto filter into 2 or 3 sections.  Basically telling it to autofill the sections independently.

mahesh · Answer

Hi... actually I been using the advanced filter in my excel sheet and its being working fine...but now since I have been upgraded to excel 2007 the excel has stopped working. I have a source data sheet, a filter criteria in another sheet & I copy the filtered rows to another sheet.
Here how the code goes


Sheets("H_DataSheet").Range("Database").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("H_fullFiletrs").Range("BigBugger"), CopyToRange:=Sheets("PIL").Range("B9:BZ9")

The code works fine in excel 2003 but doesn't returns any filtered rows......i.e the sheet comes blank after filter code.

Please if any any can suggest any thing.........
I have been stuck with this for long now...........
Please help...........

Martine · Answer

my auto filter stopped working- the dropdown box is empty and it's not over 1000 lines - I think it's something to do with blank characters when I copied/pasted from web based application, but I don't see them.

I tried to copy the whole spreadsheet to a new one - didn't work
I tried to copy some lines of the spreadsheet - didn't work

so I resorted to making a pivot table including the same columns and recreated the same spread sheet this worked! filter is functional again.

If anyone knows why this happens please advise.

Thanks,
Martine

Aissa · Answer

Aston and Craig's solution is so easy (de-activated Auto Filter, then control-A to select all data in the spreadsheet, and then re-activate the Auto Filter), and fixed my problem immediately (though I have less than 1,000 rows of data, which I believe is the threshold of Excel 03's capacity for filtering data.  Thanks both for this simple, non-techie solution!  (For some reason I cannot fathom, my excel was previously only allowing me to filter up to row 534, so my selected filter subject PLUS every row from 535 onward would show up in the spreadsheet, but thankfully it is now working correctly again!).  Cheers all!

tomosaurus · Answer

brilliant, this has been bugging me for ages! Thank you so much..

Asten · Answer

Glad we could be of help (:

Excel 2007 · Answer

This just happened in Excel 2007. Solved it by creating and saving a new Excel file, then with the mouse, dragging and dropping the workbook from the old file into the new file. This created a copy onto the new file and the filters worked again.

aamir · Answer

hi 
just show the screen or email the problem to me 
ill get back to u ?

sooon
aamirumar @ gmail.com

AndreaMetaleira · Answer

Hi all,

I had a similar problem in the past weeks - the auto filter was freezing after a couple of use and I had no other choice than to remove the autofilter and unhide the hidden rows before putting it back for a couple more uses.

A colleague then reminded me of a common practice: instead of adding a filter to the title row, insert a blank row between the row bearing the column titles and your first row of proper data, and add the filter to that blank row. Well, it works for me. I think the filter gets stuck when the column titles get too long for some reason.

ashu · Answer

after filtering data are changes like break of cells and rows

Val · Answer

I need to filter within my spreadsheet and copy data from another spreadsheet to match up with what I filtered.  But my pasted contents are also going into the cells that are not showing in my filter... Please help

oceanbaby · Answer

im using the formula to add =sum(b2:b184) and then enter but it doesnt work

Heather C · Answer

hi I tried the select all and then applied the filer again. It worked.
Heather C

Excel autofilter stops filtering

23 responses

Similar discussions

Subscribe To Our Newsletter!