Mulitple Filter Selection

[MrRJ]

Hi,
I need to strenghten my code to allow the users to select 2 areas (or
columns) in which the filter would operate. I was able to do one, but can't
find a way to do more than one.

'
Dim Message, Title, MyValue
Dim ExactDate As Date
Dim ExactCriterion As String
Message = "Please Enter the Period, (00)"
Title = "Title"
MyValue = InputBox(Message, Title)

ExactCriterion = Format(ExactDate, "mm/dd/yy")

Range("Headers").AutoFilter
Range("Headers").AutoFilter Field:=6, Criteria1:=MyValue

Please help. Thanks.

 

[Bernie Deitrick]

A sheet can only have one filter applied. If you want multiple areas, use multiple sheets, or
combine the ranges and use multiple criteria (on different columns)

HTH,
Bernie
MS Excel MVP

 

[MrRJ]

Bernie,
If I understood you correctly, there is no way that I can ask the user to
provide filter criteria? I can build a macro for variable selected filters,
see below.
Selection.AutoFilter Field:=6, Criteria1:="13"
Selection.AutoFilter Field:=7, Criteria1:="08"
Selection.AutoFilter Field:=9, Criteria1:="Direct"
Selection.AutoFilter Field:=11, Criteria1:="PBGCAN"
Selection.AutoFilter Field:=20, Criteria1:="<>NA", Operator:=xlAnd

With this concept, can I have the user be prompt to type in a value for 2
different columns?

Any ideas?

 

[Bernie Deitrick]

No. You can ask easily:

Selection.AutoFilter Field:=6, Criteria1:=CStr(InputBox("Criteria for Field 1"))
Selection.AutoFilter Field:=7, Criteria1:=CStr(InputBox("Criteria for Field 2"))

etc...


HTH,
Bernie
MS Excel MVP

 

[MrRJ]

Bernie,
Through some trial and error, I managed to pull it off. Here is the code I
used.
Dim Message, Title, MyValuePeriod
Dim ExactDate As Date
Message = "Please Enter the Period, (00)"
Title = "Title"
MyValuePeriod = InputBox(Message, Title)
Range("Headers").AutoFilter
Range("Headers").AutoFilter Field:=6, Criteria1:=MyValuePeriod


Message = "Please Enter the Year Reported, (00)"
Title = "Title"
MyValueYear = InputBox(Message, Title)
Range("Headers").AutoFilter Field:=7, Criteria1:=MyValueYear
End Sub

Thoughts?