Autofilter with Varying numbers of Criteria

[burns14hs]

Hello Guys -

I have the following macro that helps sort data based on the day of week and manager names to fill a daily expected roster of employees from a large database. The code works well if all of the defined criteria have values, but I need the code to be able to change based on the number of managers (max of 7) and the number of shifts (max of 11). I have a matrix set up on a worksheet that based on different variables like regular, 1 day of overtime, 2 days of overtime, fills in the necessary number of shifts that belong here on a day. The code, however, craps out if any of the defined variables equals "" (italicized section). Anybody have any ideas to help this code work out correctly?

Sub FilerData()

    
Dim mgr_1 As String
Dim mgr_2 As String
Dim mgr_3 As String
Dim mgr_4 As String
Dim mgr_5 As String
Dim mgr_6 As String
Dim mgr_7 As String
Dim shift_1 As String
Dim shift_2 As String
Dim shift_3 As String
Dim shift_4 As String
Dim shift_5 As String
Dim shift_6 As String
Dim shift_7 As String
Dim shift_8 As String
Dim shift_9 As String
Dim shift_10 As String
Dim shift_11 As String



mgr_1 = Worksheets("Input").Range("B3").Value
mgr_2 = Worksheets("Input").Range("B4").Value
mgr_3 = Worksheets("Input").Range("B5").Value
mgr_4 = Worksheets("Input").Range("B6").Value
mgr_5 = Worksheets("Input").Range("B7").Value
mgr_6 = Worksheets("Input").Range("B8").Value
mgr_7 = Worksheets("Input").Range("B9").Value
shift_1 = Worksheets("Shift Code Matrix").Range("L4").Value
shift_2 = Worksheets("Shift Code Matrix").Range("L5").Value
shift_3 = Worksheets("Shift Code Matrix").Range("L6").Value
shift_4 = Worksheets("Shift Code Matrix").Range("L7").Value
shift_5 = Worksheets("Shift Code Matrix").Range("L8").Value
shift_6 = Worksheets("Shift Code Matrix").Range("L9").Value
shift_7 = Worksheets("Shift Code Matrix").Range("L10").Value
shift_8 = Worksheets("Shift Code Matrix").Range("L11").Value
shift_9 = Worksheets("Shift Code Matrix").Range("L12").Value
shift_10 = Worksheets("Shift Code Matrix").Range("L13").Value
shift_11 = Worksheets("Shift Code Matrix").Range("L14").Value


    Worksheets("Roster2").Select
    Columns("A:M").Select
    Selection.AutoFilter
[I]    ActiveSheet.Range("$A$1:$M$1000").AutoFilter Field:=7, Criteria1:=mgr_1, Operator:=xlOr, Criteria2:=mgr_2, Operator:=xlOr, Criteria3:=mgr_3, Operator:=xlOr, Criteria4:=mgr_4, Operator:=xlOr, Criteria5:=mgr_5, Operator:=xlOr, Criteria6:=mgr_6, Operator:=xlOr, Criteria7:=mgr_7
    ActiveSheet.Range("$A$1:$M$1000").AutoFilter Field:=11, Criteria1:=shift_1, Operator:=xlOr, Criteria2:=shift_2, Operator:=xlOr, Criteria3:=shift_3, Operator:=xlOr, Criteria4:=shift_4, Operator:=xlOr, Criteria5:=shift_5, Operator:=xlOr, Criteria6:=shift_6, Operator:=xlOr, Criteria7:=shift_7, Operator:=xlOr, Criteria8:=shift_8, Operator:=xlOr, Criteria9:=shift_9, Operator:=xlOr, Criteria10:=shift_10, Operator:=xlOr, Criteria11:=shift_11[/I]
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[burns14hs]

Any help guys?

 

[burns14hs]

It would take some reworking, but I think this would help solve my issues: VBA autofilter using an array - ignore criteria if it is not in the filtered list - Stack Overflow

Anybody familiar with the code in that link that could verify if that is basically what I'm looking for as a solution....