Automate advanced filtering

tJ3333

New Member
Joined
Mar 7, 2011
Messages
1
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
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?
 

Forum statistics

Threads
1,141,728
Messages
5,708,137
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top