Autofilter with multiple criteria - how to ignore blank criteria

**MOONDRIFT47** · 12-28-2012, 07:37 AM

Hi,

I have the following code:

Sub searchfilter()
'
' Search page filter
'
'
    ActiveSheet.Range("$A$4:$K$6000").AutoFilter field:=3, Criteria1:=ActiveSheet.Range("c2")
    ActiveSheet.Range("$A$4:$K$6000").AutoFilter field:=6, Criteria1:=ActiveSheet.Range("D2")
    ActiveSheet.Range("$A$4:$K$6000").AutoFilter field:=8, Criteria1:=ActiveSheet.Range("E2")

End Sub

However it requires all cell ranges , C2, D2, and E2 to have a value to filter or it displays blank lines only. How do you adjust the above code to allow it to ignore a cell if there is no search criteria entered into it?

Thanks

**HaHoBe** · 12-29-2012, 07:00 AM

Hi, MOONDRIFT47,

check the length of the cells value before filtering?

Sub searchfilter2()
'
' Search page filter
'
'
    With ActiveSheet
      If Len(.Range("C2").Value) > 0 Then
        .Range("$A$4:$K$6000").AutoFilter Field:=3, Criteria1:=.Range("C2")
      End If
      If Len(.Range("D2").Value) > 0 Then
        .Range("$A$4:$K$6000").AutoFilter Field:=6, Criteria1:=.Range("D2")
      End If
      If Len(.Range("E2").Value) > 0 Then
        .Range("$A$4:$K$6000").AutoFilter Field:=8, Criteria1:=.Range("E2")
      End If
    End With
End Sub

Ciao,
Holger