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?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor 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

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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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
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.

All Courses

From novice to tech pro — start learning today.