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)
Book1.xls
wesmanbigmig13Asked:
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

Copy-of-Book1.xls
0
 
ccaltaccCommented:
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
0
 
wesmanbigmig13Author Commented:
Hi ccaltacc - no, I'm using Excel 2003 unfortunately.
0
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


Book1-attempt.xls
0
 
wesmanbigmig13Author Commented:
Hi teylyn - I got it working - just had to learn how to add the VB code correctly. Thanks for your help!
0
 
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
0
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.