I know that I'm on SuperUser and not on StackOverflow, but the solution to this problem can be found in using VBA code on Excel 2016.
I have similar (more complex) problem.
I want to add some filters on explicit columns but not on row 1 only on row 2 as you can see in following screen's capture.
I have tried using Excel GUI but this seems impossible, so I have written following code:
'********************************************************
'* SetFilter()
'********************************************************
'* PUBLIC method to call to define CUSTOM AutoFilter
'* on complex header.
'********************************************************
Sub SetFilter()
'Compute last row number
Dim nLast As Long
nLast = Range("A" & Rows.Count).End(xlUp).Row
'Lock screen update
Application.ScreenUpdating = False
'Unmerge merged cells to allow adding filter
Range("A1:A2").MergeCells = False
Range("B1:B2").MergeCells = False
Range("C1:C2").MergeCells = False
Range("D1:D2").MergeCells = False
Range("E1:E2").MergeCells = False
Range("F1:F2").MergeCells = False
'Add filter on row 2 and not 1
Range("A2:Z" & nLast).Select
Selection.AutoFilter
'Remove (or Hide) filter combobox for some columns
Selection.AutoFilter Field:=GetColumnIndex("C"), VisibleDropDown:=False
Selection.AutoFilter Field:=GetColumnIndex("G"), VisibleDropDown:=False
Selection.AutoFilter Field:=GetColumnIndex("H"), VisibleDropDown:=False
'Merge unmerged cells to restore previous state
Range("A1:A2").MergeCells = True
Range("B1:B2").MergeCells = True
Range("C1:C2").MergeCells = True
Range("D1:D2").MergeCells = True
Range("E1:E2").MergeCells = True
Range("F1:F2").MergeCells = True
'Unlock screen update
Application.ScreenUpdating = True
End Sub
'********************************************************
'* GetColumnIndex()
'********************************************************
'* return column's index from column letters
'********************************************************
Function GetColumnIndex(sColLetter As String) As Integer
Dim n As Integer: n = 0
Dim iMax As Integer: iMax = Len(sColLetter)
Dim i As Integer
Dim sChar As String
Dim c As Integer
For i = 1 To iMax
sChar = Mid(sColLetter, i, 1)
c = 1 + Asc(sChar) - Asc("A")
n = n * 26 + c
Next
If n = 1 Then
n = 1
End If
GetColumnIndex = n
End Function
The logic of this code is
A. Unmerge vertically merged header cells to allow adding filter on row
2
Range("A1:A2").MergeCells = False
Cells A1 and A2 are unmerged.
B. Add AutoFilter on all cells of row 2
Range("A2:Z" & nLast).AutoFilter
AutoFilter is generated for cells in all rows except row 1.
C. Remove or hide FILTER Combobox for some columns
Selection.AutoFilter Field:=GetColumnIndex("C"), VisibleDropDown:=False
DropBox of Column "C" is hidden.
D. Merge unmerged cells to restore original state
Range("A1:A2").MergeCells = True
Cells A1 and A2 are merged again.