Automate advanced filtering

[tJ3333]

Hi everyone! Still very new to excel, and I require help in the advanced filtering vba function.

Basically, I need to filter out specific data from a master list of data (eg, the list of members in a group). I have done a simple coding, where the filtered data is copied into another location with a click of a button.

Sub doFilter()

Range("F5:I9").Select
Selection.Clear
Range("A1:D43").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F1:I2"), CopyToRange:=Range("F4:I4"), Unique:=False

End Sub

Are there any ways that this process can be automated, such that the filtered list changes every time the criteria range is changed, without the need for a button?

Thanks!

 

[Fazza]

Hi,

Code can automatically run on 'events'. Such as, a change to a worksheet.

So, such an event code could check if the cell that has changed is in range "F1:I2", or a slightly extended range (say "F1:I5" or whatever) if you wanted. If so, then the advanced filter step could execute. Have you a reference book/info/whatever to read/learn about worksheet change event code? Google should find examples.

[Another option, if the range is always "F1:I2" I think you could set up a parameterised query that would run automatically without using code. Though a downside of this is if the file path changes, VBA would be needed to set the connection. Not a big deal though. It likely would be simpler though with a change event code.]

What do you think?