If/Elseif AutoFilter Macro

[kooldaman]

SETUP. I have a column with many different job names repeated in it and
AutoFilter has been applied to it. I also have setup a drop down list that
puts a selected job name into helper cell Q4. In the drop down list I also
have the word 'All'.

MACRO. I need a macro that does not trigger the filter if cell Q4 equals
'All', but filters the indicated job name if not equal to 'All'. Below is
what I have so far. I can't figure out the code that should go after the
first 'Then'. Also not sure if rest of macro is correct.

BIG PICTURE. Ultimately, I want to use this code sequence three times to
filter three different columns with one macro. Each will have it's own drop
down list and helper cell with 'All' or names in them to direct the filter.
Your help would be greatly appreciated.

Sub FilterData_JobName()
Application.ScreenUpdating = False
'Run (Select All) Filter before running specific filter
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4
'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4
Sheets("Time Sheet").Select
With Sheets("Time Sheet")
If Sheets("Time Sheet").Range("Q4").Value = "All" Then
'--???? need code that ends this part of macro here ????--
ElseIf Sheets("Time Sheet").Range("Q4").Value <> "All" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4,
Criteria1:=ActiveSheet.Range("Q4").Value, _
Operator:=xlOr, VisibleDropDown:=True
End With
Application.ScreenUpdating = False
End Sub

 

[JLGWhiz]

If you want it to do nothing when the helper cell = "All" then you do not
need the first part of the If ... Then statement. I would set the helper
range to an Object Variable for ease of use:

Set sRng = Sheets("Time Sheet").Range("Q4")

Then just use one If ... Then statement to test the value

If sRng.Value <> All Then
'Autofilter code here
End If

If you are using the same helper range for all three columns then you could
put the If...Then statement inside a For...Next loop like:

For i = 4 To 6 'assumes the 3 col are E, F and G
If sRng.Value <> All Then
'Autofilter code here
End If
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=i,
Criteria1:=ActiveSheet.Range("Q4").Value, _
Operator:=xlOr, VisibleDropDown:=True

Next

This is all untested and may require some editing.

 

[Dave Peterson]

Maybe...

Option Explicit
Sub FilterData_JobName()
Dim wks As Worksheet
Dim myStr As String

Set wks = ActiveSheet
'or be specific
'Set wks = Worksheets("SomeSheetNameHere")

Application.ScreenUpdating = False

With wks
'Remove any filter in field 4
.Range("B8:T453").AutoFilter field:=4

'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4
myStr = Worksheets("Time Sheet").Range("Q4").Value
If LCase(myStr) = LCase("all") Then
'don't do anything, you already did the the (All)
Else
.Range("b8:t453").AutoFilter field:=4, Criteria1:=myStr
End If
End With

Application.ScreenUpdating = False
End Sub

SETUP. I have a column with many different job names repeated in it and
AutoFilter has been applied to it. I also have setup a drop down list that
puts a selected job name into helper cell Q4. In the drop down list I also
have the word 'All'.

MACRO. I need a macro that does not trigger the filter if cell Q4 equals
'All', but filters the indicated job name if not equal to 'All'. Below is
what I have so far. I can't figure out the code that should go after the
first 'Then'. Also not sure if rest of macro is correct.

BIG PICTURE. Ultimately, I want to use this code sequence three times to
filter three different columns with one macro. Each will have it's own drop
down list and helper cell with 'All' or names in them to direct the filter.
Your help would be greatly appreciated.

Sub FilterData_JobName()
Application.ScreenUpdating = False
'Run (Select All) Filter before running specific filter
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4
'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4
Sheets("Time Sheet").Select
With Sheets("Time Sheet")
If Sheets("Time Sheet").Range("Q4").Value = "All" Then
'--???? need code that ends this part of macro here ????--
ElseIf Sheets("Time Sheet").Range("Q4").Value <> "All" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4,
Criteria1:=ActiveSheet.Range("Q4").Value, _
Operator:=xlOr, VisibleDropDown:=True
End With
Application.ScreenUpdating = False
End Sub

 

[Dave Peterson]

ps. If you decide that you want to show all the data, you don't have to go
through each column.

You can use:

with wks
'show all the data
If .FilterMode Then
.ShowAllData
End If
end with

(After all that other stuff where wks is assigned.)

SETUP. I have a column with many different job names repeated in it and
AutoFilter has been applied to it. I also have setup a drop down list that
puts a selected job name into helper cell Q4. In the drop down list I also
have the word 'All'.

MACRO. I need a macro that does not trigger the filter if cell Q4 equals
'All', but filters the indicated job name if not equal to 'All'. Below is
what I have so far. I can't figure out the code that should go after the
first 'Then'. Also not sure if rest of macro is correct.

BIG PICTURE. Ultimately, I want to use this code sequence three times to
filter three different columns with one macro. Each will have it's own drop
down list and helper cell with 'All' or names in them to direct the filter.
Your help would be greatly appreciated.

Sub FilterData_JobName()
Application.ScreenUpdating = False
'Run (Select All) Filter before running specific filter
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4
'Filter Col-E [Field:=4] (Job Name) Ref. cell Q4
Sheets("Time Sheet").Select
With Sheets("Time Sheet")
If Sheets("Time Sheet").Range("Q4").Value = "All" Then
'--???? need code that ends this part of macro here ????--
ElseIf Sheets("Time Sheet").Range("Q4").Value <> "All" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4,
Criteria1:=ActiveSheet.Range("Q4").Value, _
Operator:=xlOr, VisibleDropDown:=True
End With
Application.ScreenUpdating = False
End Sub

 

[kooldaman]

Dear JLGWhiz,
Thank you so much. Your code worked perfectly. So simple! The only change
I had to make was to put quotation marks around the word "ALL". I can now
filter three columns with any combination of three different criteria. For
anyone who might be interested, I've included my entire macro below. Note:
The reason for the double AutoFilter runs at each column is that there is a
subtotal calculation that occurs after the first run. The second run picks
up the result of the calculation.

Sub FilterData_MultipleColumns()

Application.ScreenUpdating = False

'Unfilter applicable Fields before filtering new criteria
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=15

'1. Filter Col-D [Field:=3] (PERSON) Ref. cell P4
Set sRng = Sheets("Time Sheet").Range("P4")
If sRng.Value <> "ALL" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("P4").Value
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=3, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("P4").Value
End If

'2. Filter Col-E [Field:=4] (JOB NAME) Ref. cell Q4
Set sRng = Sheets("Time Sheet").Range("Q4")
If sRng.Value <> "ALL" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("Q4").Value
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=4, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("Q4").Value
End If

'3. Filter Col-O [Field:=14] (PAY PERIOD) Ref. cell O5
Set sRng = Sheets("Time Sheet").Range("O5")
If sRng.Value <> "ALL" Then
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("O5").Value
ActiveSheet.Range("$B$8:$T$453").AutoFilter Field:=14, Criteria1:= _
"=INCLUDED", Operator:=xlOr, Criteria2:=ActiveSheet.Range("O5").Value
End If

Application.ScreenUpdating = True

End Sub