Filter and Sort with Multiple Headers

[pawcoyote]

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?

24650

[Paul_Hossler]

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

[akuini]

Hide the second row > sort > unhide

[Paul_Hossler]

Maybe simpler




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

[pawcoyote]

Hi,

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


Maybe simpler




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

[Paul_Hossler]

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




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






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

[akuini]

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

[pawcoyote]

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..
Hi, pawcoyote
Did you try my method in post #3?

[pawcoyote]

Thank you very much for all your help!
Probably because 1) I can't count, and 2) I didn't have a good enough test case




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






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