Sorting columns horizontally

[thepcer]

I want to horizontally sort vertical groups (or columns) of data. I have found the following code to work perfectly for vertically sorting rows :

Sub Macroname()
    Columns("A:D").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub Macrocity()
    Columns("A:D").Sort Key1:=Range("c2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub Macroage()
    Columns("A:D").Sort Key1:=Range("d2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub Macrounsort()
    Columns("A:D").Sort Key1:=Range("a2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

There are four columns and you can add as many rows as you want. Each function is assigned to one of the four buttons and so the buttons sort the rows starting from the second row, leaving the first row with criteria names intact. I've tried to adapt it to my need:

Sub Macroname()
    Rows("1:4").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Macrocity()
    Rows("1:4").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Macroage()
    Rows("1:4").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Macrounsort()
    Rows("1:4").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

to work with four rows and however many columns, and it does work, except it doesn't exclude the first column with the criteria names; it sorts everything. Could someone please tell me what I'm doing wrong? Much obliged.

 

[thepcer]

I've found out that the following works quite how I wanted:

Sub Crit1()
    Columns("B:ZZ").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Crit2()
    Columns("B:ZZ").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Crit3()
    Columns("B:ZZ").Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

Sub Crit4()
    Columns("B:ZZ").Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

BUT it's very limited, as the last recognized column is "ZZ" so if anyone can "fix" the 2nd code above, which is open-ended, it would still work out better for me.

 

[thepcer]

Finalized, perfectly working code:

Sub BiSortButton()
    Dim r As Range
    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

    Static SortOrder As Integer

    If SortOrder = xlAscending Then
        SortOrder = xlDescending
    Else
        SortOrder = xlAscending
    End If

    Set wpRange = Range("B1:B25")
    Set wpRange = Range(wpRange, wpRange.End(xlToRight))
    
    wpRange.Sort Key1:=Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 0)), Order1:=SortOrder, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight

End Sub

This code is for a non-hardcoded button (or object), to be placed between rows 1-25 in column A and will sort all the columns to the left, starting with column B, based on the data on the current the row. Activating the button/object will select the overlapped cell (ie. the cell under the button) as that was necessary to avoid hardcoding one dedicated button per row (and 25 of them, in this case). You can sort by ascending and descending order by clicking the same button.

Working example:
http://tinyurl.com/23kfeya