Advanced FIlter Criteria using multiple rows and single header.

[Rfriend]

Hello,

I am trying to use an advanced filter to return all values in a criteria range. I can get data for the values in the criteria, but I cannot get the filter to not return values for the range cells that are blank. Listbox 1 & 2 are fine. But listbox3 filters on a single column with up to 13 related codes. It sees the blank cells as returns all personnel instead of only returning values for the jobcclass that have a value. Since the criteria for each job is different, the list of jobclass codes returned will change anywhere between 1 to 13 codes. My code is below. Any help is appreciated.

For Example:

• I select either vacant or active positions in lstbox1 using an advfilter.
• Double click the position in lstbox1 which inserts the jobclass into the txtbox "Search Job Class" on the user form.
• Click search to see a list of all the positions and their corresponding job description requirements that would be considered for promotion to the vacant position.
• Show all available active staff that possess any of the jobclasses in lstbox2 and sort them by years of service, or seniority.

Sub AdvFilterVaclstBox1()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("VacantOut!Criteria"), CopyToRange:= _
        Range("VacantOut!Extract"), Unique:=False
End Sub

Sub AdvFilterVaclstBox2()
    With Sheet14
        Range("Table297[#All]").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
        :=Range("JDOut!Criteria"), CopyToRange:=Range("Table21[#All]"), Unique:= _
        False
    Range("Table21").Select
    Selection.Copy
    Range("V11").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    End With
End Sub

Sub AdvFilterVaclstBox3()
    Sheets("Staff_Data").Range("Table735[#All]").AdvancedFilter Action:= _
        xlFilterCopy, CriteriaRange:=Range("JDOut!Criteria"), CopyToRange:=Range( _
        "V27:AN27"), Unique:=False
End Sub


Private Sub cmdSearchStatus_Click()
    Dim Status As Variant
        On Error GoTo errHandler:
        lstBox1.RowSource = ""
    Set Status = cboSearchStatus
        Application.ScreenUpdating = False
    If Me.cboSearchStatus.Value = "Active" Or Me.cboSearchStatus.Value = "Inactive" Or Me.cboSearchStatus.Value = "Vacant" Then
     Sheet3.Range("D5").Value = Me.cboSearchStatus.Value
     AdvFilterVaclstBox1
    If Sheet3.Range("C9").Value = "" Then
        lstBox1.RowSource = ""
    Else
        lstBox1.RowSource = "VacantOut"
    End If
        Exit Sub
    End If
        AdvFilterVaclstBox1
    If Sheet3.Range("C9").Value = "" Then
        lstBox1.RowSource = ""
    Else
      lstBox1.RowSource = "VacantOut"
    End If
      On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "No match found for " & cboSearchStatus.Value
    Me.lstBox1.RowSource = ""
    Exit Sub
End Sub

Private Sub cmdJDLookup_Click()
    Dim JobClass As Long
    Dim JDOutSH As Worksheet
    Dim Staff_Data As Worksheet
        On Error GoTo errHandler:
    Set JDOutSH = Sheet14
    Set Staff_Data = Sheet7
    JobClass = Me.CS2.Value
        JDOutSH.Range("C7").Value = Me.CS2.Value
    Application.ScreenUpdating = False
        lstBox2.RowSource = "JDSrchNew"
        'Transpose
    With Sheet14
        If CS2.Value = "" Then
            .Range("C7").Value = ""
        Else
            .Range("C7").Value = CS2
        End If
    End With

        If Sheet14.Range("C7").Value = "" Then
            lstBox2.RowSource = ""
        Else
            lstBox2.RowSource = "JDSrchNew"
        End If
        
        If Sheet14.Range("C7").Value = "" Then
            lstSelector.RowSource = ""
        Else
            lstSelector.RowSource = "PotentialStaff"
        End If
            AdvFilterVaclstBox2
                AdvFilterVaclstBox3
                   lstBox2.RowSource = Sheet14.Range("JDSrchNew").Address(external:=False)
                lstSelector.RowSource = Sheet14.Range("PotentialStaff").Address(external:=True)
        Exit Sub
errHandler:
    MsgBox "No match found for " & txtSearchTrng2.Text
        On Error GoTo 0
    Exit Sub
End Sub