filter a table by combination of selected cell values using VBA - syntax error

Question

Can you please help me with the code below, i keep getting a syntax error

The objective of the macro is filter by multiple rows selection under same column.

The lines i am getting a syntax error on are:

sColumn(n) = sColumn(n) & _
If(sColumn(n) vbNullString, ",", "") & oCell.Text


If sColumn(n) vbNullString Then

The full code is below:

Sub combinationFilter()

Dim oRange As Range
Dim oArea As Range
Dim oCell As Range
Dim oLO As ListObject
Dim sColumn() As Variant
Dim n As Long

' Create Filter
Set oLO = Selection.ListObject

If Not oLO Is Nothing Then

    ReDim sColumn(1 To oLO.ListColumns.Count)

    Set oRange = Intersect(Selection, oLO.DataBodyRange)

    For Each oArea In oRange.Areas
        For Each oCell In oArea.Cells
            n = oCell.Column - oLO.Range.Column + 1
            sColumn(n) = sColumn(n) & _
            IIf(sColumn(n) vbNullString, ",", "") & oCell.Text
        Next oCell
    Next oArea

    ' Apply Filter
    For n = LBound(sColumn) To UBound(sColumn)
        If sColumn(n) like vbNullString Then
            oLO.Range.AutoFilter _
            Field:=n, _
            Criteria1:=Split(sColumn(n), ","), _
            Operator:=xlFilterValues
        End If
    Next n

End If

End Sub

I am also getting a mismatch error on the below lines:

If sColumn(n) like vbNullString Then
oLO.Range.AutoFilter _
Field:=n, _
Criteria1:=Split(sColumn(n), ","), _
Operator:=xlFilterValues

Thank you in advance.

Answer 1

You need an IIF function, not an IF.

sColumn(n) = sColumn(n) & _
  IIF(CBool(Len(sColumn(n))), ",", vbNullString) & oCell.Text

Additionally, your sColumn array may be null/vbnullstring/empty string. You only want to attempt the Range.AutoFilter Method if there is something in sColumn(n), not if there isn't.

' Apply Filter
For n = LBound(sColumn) To UBound(sColumn)
    If sColumn(n) <> vbNullString Then
        oLO.Range.AutoFilter _
        Field:=n, _
        Criteria1:=Split(sColumn(n), ","), _
        Operator:=xlFilterValues
    End If
Next n

You cannot split an empty array element (e.g. vbNullString) into anything so you are getting the Error 13: Type mismatch.

Answer 2

Pertinent to your case, the correct syntax could be like the following:

sColumn(n) = sColumn(n) & IIf(IsNull(sColumn(n)), "", ",") & oCell.Text

or, as pointed out by member @Jeeped, using Like operator instead of Is (or using equal sign "="):

sColumn(n) = sColumn(n) & IIf(sColumn(n) Like vbNullString, "", ",") & oCell.Text

Hope this may help.