Hi
Followed this thread and used the example to great effect. However If i wanted to automate against a number of different filters in the datasheet ie Range A1:A2 and range B1:B2 looking at different columns to combine a view - is that possible ?
thanks
Keith
Dave wrote:
Re: Can Advanced Filter be automated?
28-Jul-07
Thanks I got it to work
Previous Posts In This Thread:
Can Advanced Filter be automated?
I am testing Advanced Filtering for an application. I read the Microsoft
article
http://office.microsoft.com/en-us/excel/HP100739421033.aspx
Unless I am doing something wrong, it appears that every time I want to
change a filter criteria, I must go to Data|Filter|Advanced Filter
Can the filter criteria just be input into the cell range and the results
change without going to Data|Filter|Advanced Filter each time?
Is there a way to automate this process?
Thanks
Dave
record a macro and assign to a button/shapeSub Macro2()'' Macro2 Macro' Macro
record a macro and assign to a button/shape
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 7/28/2007 by Donald B. Guillett
'
'
Range("B1:C8").Select
Range("B1:C8").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range _
("D1
2"), Unique:=False
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Dave,You need to use an event, combined with a macro.
Dave,
You need to use an event, combined with a macro.
For example, copy this change event code, right-click the sheet tab, select
"View Code" and paste into the window that appears:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$2" Then FilterMacro
End Sub
Put this into a regular codemodule:
Sub FilterMacro()
On Error Resume Next
ActiveSheet.ShowAllData
Range("A4
11").AdvancedFilter action:=xlFilterInPlace, CriteriaRange:=Range
_
("F1:F2"), Unique:=False
End Sub
In this example, F1:F2 has the criteria, so cell F2 is the cell that is
changed to change the filter value, and A4
11 has the table.
HTH,
Bernie
MS Excel MVP
I tried and obviously got stuck.
I tried and obviously got stuck.
Do I need to include the Macro from Don as well at the code from Bernie.
I put Bernies code in. My criteria is in D1
2
My Data is in A6:E24
So I placed this in the View Code on Sheet1 where I am doing all of this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then FilterMacro
End Sub
And I put this in Module1
Sub FilterMacro()
On Error Resume Next
ActiveSheet.ShowAllData
Range("A6
24").AdvancedFilter action:=xlFilterInPlace, CriteriaRange:=Range
_
("D1
2"), Unique:=False
End Sub
I got a complie errror when I entered
_
("D1
2"), Unique:=False
I also get a high secuirty. Is htere a way for me to sign a macro to
prevent this?
Dave
Re: Can Advanced Filter be automated?
There are sample files here with automated advanced filters:
http://www.contextures.com/excelfiles.html
Under Filters, look for 'FL0001 - Product List by Category' or 'FL0008-
Filter Rows for Text String'
If you set security to medium, you can enable the macros when you open
the workbook.
Dave wrote:
--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html
Dave,The error that you saw is due to the line wrapping introduced by my
Dave,
The error that you saw is due to the line wrapping introduced by my
newsreader...
Range("A6
24").AdvancedFilter action:=xlFilterInPlace, CriteriaRange:=Range
_
("D1
2"), Unique:=False
Should be
Range("A6
24").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("D1
2"), _
Unique:=False
You would be better off using Medium Security and allowing macros. Signing
and trusting a source can be hard to implement for users of different
abilities.
HTH,
Bernie
MS Excel MVP
Re: Can Advanced Filter be automated?
Thanks I got it to work
Submitted via EggHeadCafe - Software Developer Portal of Choice
Join Lists with LINQ - SharePoint 2010
http://www.eggheadcafe.com/tutorial...6e-7d3fb7d38eca/join-lists-with-linq--sh.aspx
