Filter and Sort with Multiple Headers

Good afternoon,

I have been reading that this is frowned upon but I have the following setup in a excel spreadsheet and would like to be able to sort it without having the 2nd Header included in the Sort and being moved through out the spreadsheet.

I want to be able to Sort using the Client Name Header. I cannot move the Bottom header above the top header, which everyone is telling me to do. Is there a way to Filter and Sort using the top header without causing the bottom header to move?

Attachment 24650

Move row 2 above row 1, sort row 2-n, and then put the new row 1 back where it started as row 2

Hide the second row > sort > unhide

Maybe simpler

Code:

---

Sub Macro1()
    Dim r As Range
   
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
   
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


---

Hi,

I tried this and it keeps putting the 2nd header at the bottom of the sort once done.

Quote:

---

Originally Posted by Paul_Hossler

Maybe simpler

Code:

---

Sub Macro1()
    Dim r As Range
   
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 1, r.Columns.Count)
   
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


---

---

Probably because 1) I can't count, and 2) I didn't have a good enough test case

Code:

---

    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 2, r.Columns.Count)

---

Code:

---

Option Explicit

Sub Macro1()
    Dim r As Range
   
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 2, r.Columns.Count)
   
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


---

Hi, pawcoyote
Did you try my method in post #3?

Yes, I can do that but that isn't what I want to be able to do. I want to be able to sort from the first header without having to change anything. I know my structure isn't the best but you would think you could do this easily..

Quote:

---

Originally Posted by akuini

Hi, pawcoyote
Did you try my method in post #3?

---

Thank you very much for all your help!

Quote:

---

Originally Posted by Paul_Hossler

Probably because 1) I can't count, and 2) I didn't have a good enough test case

Code:

---

    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 2, r.Columns.Count)

---

Code:

---

Option Explicit

Sub Macro1()
    Dim r As Range
   
    Set r = ActiveSheet.Cells(1, 1).CurrentRegion
    Set r = r.Cells(3, 1).Resize(r.Rows.Count - 2, r.Columns.Count)
   
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=r.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange r
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub


---

---