Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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.

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.