Advanced Filter - Refresh

[Giskard]

Hi All,

I set up an Advanced filter on a small amount of data and it works great. However, when I update my Criteria(from ="=1" to ="=2") the filter does not automatically update.

To update I have to go into advanced filter and just press okay.

How do I make Advanced Filter Automatically update?


-Giskard

 

[hkaplan2]

You can create a macro that performs the Advanced Filter | OK steps. The copy that code to the worksheet SelectionChange event. Something like this (Edit to suit your ranges):


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Range("A1:B8").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
("E12:F13"), Unique:=False

End Sub


Every time you edit the criteria and press Enter, the filter updates.

 

[Giskard]

Thank you. Is there a way to do this without macros? As in my organization some people are using 2007 and thus would get warnings. Further explaining to someone 2 pay grades above me what VBA is not a task I look forward to.

I am actually using this advance filter for affectively a conditional hide function, where if cell X=1, rows 5-9 become hidden. If there is another way to do this without Advanced filter I am all ears.


P.S. I am using 2003.

Thank you once again.

-Giskard

 

[hkaplan2]

Then try regular autofilter. Data | Filter | Autofilter

When you use this, select "custom" from the drop down box in the field you want the condition, and set the "equals" or "greater than" or whatever condition you want.

You would need to teach them how to use this feature, but there is no VBA.

 

[Giskard]

Thanks for the quick reply.

I guess I will need to figure out another method. It just one of those things that require a "no touch" spreadsheet.


By the way do you know of any other way to hide cells, without invoking macros?

-Giskard