Bdtyktl

I have a spreadsheet which uses 2 rows for header information and some of the columns have the 2 rows merged together as one cell. I a multiple columns merged together in the first row, but the second row underneath without the columns merged together. Filtering implicitly is assuming only 1 row of header information. Sorting has a header row check-box but that only toggles treating the first row as a header.

Is there a way to make excel treat the first two rows as headers?

Try this:

1. Unmerge the headers that are merged.


2. Select your data manually, from the second row downwards.


3. Apply the filter.


4. Re-merge the headers.

The filter dropdowns should stay on row 2.

You need to ensure that the value in advanced filter Field Range is set to include the rows below your header rows.

You can set this value manually or use a macro to do it for you.

EG in my spreadsheet, I have rows 1-3 as multi line headers. I constantly add rows of data below. The following macro sets the Filter Field Range to include rows 4 - to the last populated row on the spreadsheet. This ensures that any sort will treat row 3 as the header (provided you check the 'my data includes headers' checkbox)

(I incorporate this at the end of a more complex macro, to ensure that the Field Range property is always up to date after data imports - easier than remembering to manually re-set it each time)

Sub selector()



Sheets("AESummary").Select

Sheets("AESummary").Activate



' find last row containing data in column A (column A will always be populated in this dataset)



Set WS = Worksheets("AESummary")

With WS

    Set LastCellC = .Cells(.Rows.Count, "A").End(xlUp)

        LastCellRowNumber = Application.WorksheetFunction.Max(LastCellC.Row) + 1

   End With



' set the filter Field Range value to include Row 4 to the last populated row in the worksheet



        Rows("4:" & LastCellRowNumber).AdvancedFilter Action:=xlFilterInPlace, Unique:=False





End Sub

Well, better late than never

Define a local (worksheet) name "Database" from the last header row to the last table row. That is:

Formulas » Name Manager » New » Name:Database, Scope:{sheet name}, Refers to:{select all table and manually change first cell's row}

sad to say that XLS 2016 still doesnt handle this OOB.
there is another workaround to this, which should also work with previous versions:

separate the last header row from the other ones with an empty line and hide this one. then XLS has no trouble in recognizing this one row as he header.