Flitering Macro

[Ziro54]

Afternoon

I am using a macro to filter several arguments.

Using 4 cells to define my filters. But if I leave any of them blank, the filter returns with no results.

How do I write the macro to ignore if the cell is left bank?

Sub Filter()'
' Filter Macro
'


'
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=8, Criteria1:= _
        Range("$D$2")
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=11, Criteria1:= _
        Range("$E$2")
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=10, Criteria1:= _
        Range("$F$2")
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=12, Criteria1:= _
        Range("$G$2")
     
        
End Sub

Thank you in advance.

 

[Joe4]

Maybe try putting each statement within an IF statement, i.e.
change:

    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=8, Criteria1:= _
        Range("$D$2")

to

    [COLOR=#ff0000]If Range("$D$2")<>"" Then [/COLOR]ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=8, Criteria1:= _
        Range("$D$2")

and repeat the same logic for the other three statements.

 

[BarryL]

Joe4's solution is probably the best. I wrapped it in multiple case statements ....probably not the best idea

Sub Filter() '


Select Case False


Case IsEmpty(Range("D2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=8, Criteria1:= _
        Range("$D$2")
        
End Select


Select Case False
Case IsEmpty(Range("E2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=11, Criteria1:= _
        Range("$E$2")
        
        
End Select
Select Case False
Case IsEmpty(Range("F2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=10, Criteria1:= _
        Range("$F$2")
        
End Select
Select Case False
Case IsEmpty(Range("G2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=12, Criteria1:= _
        Range("$G$2")
End Select


End Sub

 

[dmt32]

untested but try

Sub Filter() '
' Filter Macro
'
    Dim cell As Range
    Dim i As Integer


'
    For Each cell In Range("D2,E2,F2,G2").Cells
        i = i + 1
        If Len(cell.Value) > 0 Then
            Range("$A$3:$FM$301").AutoFilter Field:=Choose(i, 8, 11, 10, 12), Criteria1:=cell.Value
        End If
    Next cell
End Sub

Dave

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1250318-skip-blanks.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Ziro54]

Thank you very much. Works perfectly!!!!

Joe4's solution is probably the best. I wrapped it in multiple case statements ....probably not the best idea

Sub Filter() '


Select Case False


Case IsEmpty(Range("D2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=8, Criteria1:= _
        Range("$D$2")
        
End Select


Select Case False
Case IsEmpty(Range("E2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=11, Criteria1:= _
        Range("$E$2")
        
        
End Select
Select Case False
Case IsEmpty(Range("F2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=10, Criteria1:= _
        Range("$F$2")
        
End Select
Select Case False
Case IsEmpty(Range("G2").Value)
    ActiveSheet.Range("$A$3:$FM$301").AutoFilter Field:=12, Criteria1:= _
        Range("$G$2")
End Select


End Sub

 

[Joe4]

Note that Case statements are great when you have multiple options that each value can be equal to, but are a bit overkill when you are just checking for one value.
If you had followed the methodology I suggested (or the one dmt32) suggested, your code would be substantially shorter.

Sub Filter() '
' Filter Macro
'

    If Range("$D$2") <> "" Then ActiveSheet.Range("$A$3:$FM$301").AutoFilter _
        Field:=8, Criteria1:=Range("$D$2")
    If Range("$E$2") <> "" Then ActiveSheet.Range("$A$3:$FM$301").AutoFilter _
        Field:=11, Criteria1:=Range("$E$2")
    If Range("$F$2") <> "" Then ActiveSheet.Range("$A$3:$FM$301").AutoFilter _
        Field:=10, Criteria1:=Range("$F$2")
    If Range("$G$2") <> "" Then ActiveSheet.Range("$A$3:$FM$301").AutoFilter _
        Field:=12, Criteria1:=Range("$G$2")
     
End Sub