Hotkey to open filter drop down menu in merged cell in Excel 2010

Question

I use Excel 2010 and use Alt+Down arrow to open filter drop down menus.

Sometimes my header contains vertically merged cells (e.g. autofilters are in the second row and in some columns cells in rows 1 and 2 are merged) and in this case pressing Alt+Down doesn't open the menu, probably because upper cell is "selected" according to excel, but filter is in the second row.

Does a hotkey exist for this? Or is it possible to open the menu using VBA?

Answer 1

I don't think there's any solution so long as the header is merged. I propose a workaround: Un-merge the header cells, format things so it still appears the same although it's now two cells, and use VBA so the user can't select the first row.

For instance, if the cells A1:A2 were the header, the VBA to ensure that A1 can't be the active cell could be this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        If ActiveCell.Address = Range("A1").Address Then
            If Target.Cells.Count = 1 Then
                Range("A2").Select
            Else
                Union(Target, Range("A2")).Select
                Range("A2").Activate
            End If
        End If
    End If
End Sub

Note that this code could stand to be optimized a bit more. For instance, if you select all of Row 1, then this code selects all of Rows 1 & 2. However, this should be enough for you to figure out if the idea will work.