VBA AutoFilter - Criteria = Array. Select all if array is left blank

Question

I have a macro that I am using to AutoFilter a range based on data entered into multiple cells. Cell A2 contains the text "Enter Zip Code" and the Zip Code is to be entered in B2. Cell A4 contains the text "Enter Service(s)" and the Service is to be entered in B4,B5,B6,and B7.

If I leave cells B4,B5,B6, and B7 blank, the filter does not return any data. However, I am trying to have the filter select all if those cells are blank. I originally had cell B4 as a drop down with the 5 possible selections, and I used the following code to select all if left blank:

My_Range.AutoFilter Field:=11, Criteria1:="=" & Range("B2").Value
My_Range.AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B4").Value) = "", "<>", "=") & Range("B4").Value

However, I would like the option to filter various combinations based on what data is entered into those cells (B4,B5,B6,B7). In order to do this I changed the code to the following:

My_Range.AutoFilter Field:=11, Criteria1:="=" & Range("B2").Value
My_Range.AutoFilter Field:=1, Criteria1:=Array(Range("B4").Value, Range("B5").Value, Range("B6").Value, Range("B7").Value), Operator:=xlFilterValues

This code gives me the desired result, but if the cells are left blank, the filter does not return anything since it is searching for empty cells in the "Service" column in the range.

Is there a way to use the Array for the Criteria but select all if blank?

Gary's Student Gary's Student · Accepted Answer · 2016-12-29T17:38:52

Just test for the condition you wish to trap:

If Len([B4] & [B5] & [B6] & [B7]) = 0 Then
    My_Range.AutoFilter Field:=1
Else
    My_Range.AutoFilter Field:=1, Criteria1:=Array(Range("B4").Value, Range("B5").Value, Range("B6").Value, Range("B7").Value), Operator:=xlFilterValues
End If

VBA AutoFilter - Criteria = Array. Select all if array is left blank

1 Answers