Excel autofilter stops filtering [Solved/Closed]
Lucy - - Latest reply: Verythankful - May 30, 2018 at 04:41 PM
Related:
- Excel filter not working
- Filter not working in excel - Best answers
- Excel filter stops halfway - Best answers
- Excel filter formula ✓ - Forum - Excel
- Excel filter based on cell value ✓ - Forum - Excel
- Excel - Filter data through a formula - How-To - Excel
- Excel Filter/Macro ✓ - Forum - Excel
- Excel filter by day of week ✓ - Forum - Excel
20/29 replies
Best answer
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
The above solution does work however, but I Think this is much easier
We've been solving this problem for many of our customers and so far it has always worked.
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
The above solution does work however, but I Think this is much easier
We've been solving this problem for many of our customers and so far it has always worked.
Glad we were able to help! Love us? Write us a review! Rate CCM
3818 users have said thank you to us this month
View all 12 comments
Report
Awesome..That suggestion worked! Thank you so much.
Report
Saved my day thanks!
Report
It worked. Thanks
Report
Respond to Dottie
Thank you - this worked wonderfully!
Report
Respond to Verythankful
Thanks!!! This worked!
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.
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.
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
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
Hi roulette_21
Which Excel are you using, 2003 or 2007?
If 2003, I guess this may be the answer to your problem:
"Excel 2003 limits the list to 1000 visible unique items (http://support.microsoft.com/kb/q295971/)"
So MS suggests:
"To work around this issue, use the AutoFilter command on several smaller sections of the data instead of on all the data at the same time. All list items will be visible if each subset has no more unique members than the limit for the particular version of Excel."
I know another method which splits your list into smaller lists with less than 1000 items but could not remember exactly how to do it. (Please google).
Another way to check is to use the trial version of Excel 2007 from MS download website. Excel 2007 lifts the limit from 1,000 to 10,000.
Please let me know if you solve the problem or not, thanks.
Which Excel are you using, 2003 or 2007?
If 2003, I guess this may be the answer to your problem:
"Excel 2003 limits the list to 1000 visible unique items (http://support.microsoft.com/kb/q295971/)"
So MS suggests:
"To work around this issue, use the AutoFilter command on several smaller sections of the data instead of on all the data at the same time. All list items will be visible if each subset has no more unique members than the limit for the particular version of Excel."
I know another method which splits your list into smaller lists with less than 1000 items but could not remember exactly how to do it. (Please google).
Another way to check is to use the trial version of Excel 2007 from MS download website. Excel 2007 lifts the limit from 1,000 to 10,000.
Please let me know if you solve the problem or not, thanks.
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...........
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...........
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?
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!
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.
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.
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
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