Automatically update (refresh) auto-filter in Excel

Hi Experts

I have a simple Excel spreadsheet (attachment called Book1.xls). As you can see, I have 2 sheets in the workbook. Sheet 1 contains a list of items together with a quantity for each item. Sheet 2 contains an Auto-Filtered display of the list on Sheet 1. What I need to do is set the list of results on Sheet 2 to show only the items on Sheet 1 that have got a quantity value assigned to them. The way I have done this is to use a simple IF statement in the cells on Sheet 2 that shows the values from Sheet 1 for each item that has a quantity, and shows a blank for all items that do not have a quantity value. I have got that formula working well.

The problem I am having is that when I use the auto-filter to filter to show "NonBlanks" only, it doesn't refresh itself automatically - ie I have to manually select NonBlanks from the filter drop down on Sheet 2 to get it to update itself. I would like this to happen automatically, so that if/when for example I add a quantity for an item on Sheet 1 that previously had no quantity value, when I go to Sheet 2, the auto-filtered list will automatically be updated, without me having to click NonBlanks on the dropdown box every time.

How can I do this?

(I have allocated 500 Points to this question)
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello wemanbigmig13,

on Sheet2, create a macro for the Activate event.

Private Sub Worksheet_Activate()
    Selection.AutoFilter Field:=1, Criteria1:="<>"
End Sub

It will update the filter whenever you select the sheet.
See attached

cheers, teylyn


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
Is this office 2007?  If so you can click "REAPPLY" at the top of the DATA tab and it will refresh the filter and show the stuff that's changed from Sheet1
wesmanbigmig13Author Commented:
Hi ccaltacc - no, I'm using Excel 2003 unfortunately.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

wesmanbigmig13Author Commented:
Hi teylyn - that looks like a perfect solution, however I have tried adding the code and I can't get it working in my spreadsheet - I can see that it works in yours, but I can't get it going in mine. Can you please have a look at the updated XLS of mine (Book1-attempt.xls) and let me know what I have done wrong.

Many thanks

wesmanbigmig13Author Commented:
Hi teylyn - I got it working - just had to learn how to add the VB code correctly. Thanks for your help!
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
You have put the code into a standard module. Instead, it needs to go into the Sheet module for the sheet with the autofilter, in this case Sheet2, like my attached example shows.

cheers, teylyn
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.