Can Advanced Filter be automated?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 28 Jul 2007 13:31 GMT

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

Reply to this Message

Don Guillett - 28 Jul 2007 13:52 GMT

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:D2"), Unique:=False
End Sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

>I am testing Advanced Filtering for an application. I read the Microsoft
>article
[quoted text clipped - 11 lines]
>
> Dave

Reply to this Message

Bernie Deitrick - 28 Jul 2007 13:57 GMT

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:D11").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:D11 has the table.

HTH,
Bernie
MS Excel MVP

>I am testing Advanced Filtering for an application. I read the Microsoft
>article
[quoted text clipped - 11 lines]
>
> Dave

Reply to this Message

Dave - 28 Jul 2007 14:40 GMT

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:D2

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:D24").AdvancedFilter action:=xlFilterInPlace, CriteriaRange:=Range
_
("D1:D2"), Unique:=False
End Sub

I got a complie errror when I entered
_
("D1:D2"), Unique:=False

I also get a high secuirty. Is htere a way for me to sign a macro to
prevent this?

Dave

> Dave,
>
[quoted text clipped - 39 lines]
>>
>> Dave

Reply to this Message

Debra Dalgleish - 28 Jul 2007 16:21 GMT

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.

> I tried and obviously got stuck.
>
[quoted text clipped - 72 lines]
>>>
>>>Dave

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Bernie Deitrick - 28 Jul 2007 17:57 GMT

Dave,

The error that you saw is due to the line wrapping introduced by my
newsreader...

Range("A6:D24").AdvancedFilter action:=xlFilterInPlace, CriteriaRange:=Range
_
("D1:D2"), Unique:=False

Should be

Range("A6:D24").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("D1:D2"), _
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

>I tried and obviously got stuck.
>
[quoted text clipped - 73 lines]
>>>
>>> Dave

Reply to this Message

Dave - 28 Jul 2007 21:02 GMT

Thanks I got it to work

:-)

>I tried and obviously got stuck.
>
[quoted text clipped - 73 lines]
>>>
>>> Dave

Reply to this Message