macro to sort left to right instead of up and down?

[tonywatsonhelp]

Hi Everyone,

I have a range of 10 rows

AA11:AZ21

AA11 :AA21 is the headers, with the data going along the rows.
I want to do a data sort macro that will sort the ten rows right to left (first alphabetically to last)
now my ideal situation would be this
I have 20 small shapes as buttons each call "ONbt" or "OFFbt" (With a number 1 to 10 so first is "Onbt1"

now if we select an "ONbt" button then it sorts by that row, then if i press a second "ONbt" it sorts by that row also, and so on.
pressing any OFFbt will clear all sorts. (but i can do that part )

So if the above is not possible or two complex just the code to sort left to right will be a big help

Thanks

Tony

 

[Herakles]

This could help you.

Call the code with this line changing the range passed to the procedure as appropriate:

Call subSortRightToLeft(Worksheets("PO Data").Range("AB11:AK21"))

Private Sub subSortRightToLeft(rngRangeToSort As Range)
Dim WsHelper As Worksheet
Dim rngRow As Range
        
    Set WsHelper = Worksheets("Helper")
    WsHelper.cells.clear
    WsHelper.range("A1")="Helper"
       
    For Each rngRow In rngRangeToSort.Rows
        
        WsHelper.Range("A2:A" & rngRow.Columns.Count + 1).Value = Application.Transpose(Application.Transpose(Application.Transpose(rngRow)))
        
        WsHelper.Range("A1:A" & rngRow.Columns.Count + 1).Sort Key1:=WsHelper.Range("A1"), Order1:=xlDescending, Header:=xlYes
                
        rngRow.Value = Application.Transpose(WsHelper.Range("A2:A" & rngRow.Columns.Count + 1))
    
    Next rngRow
        
End Sub

 

[MARK858]

So if the above is not possible or two complex just the code to sort left to right will be a big help

Sub SORTIT()
    Dim i As Long
    Application.ScreenUpdating = False
    ActiveSheet.Sort.SortFields.Clear
    For i = 11 To 21
        Range(Cells(i, 28), Cells(i, 52)).Sort Key1:=Cells(i, 28), Order1:=xlAscending, Header:=xlNo, _
                                                 OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    Next
    Application.ScreenUpdating = True
End Sub

Edit: added the Sortfields.Clear line

 

[MARK858]

To assign it to a shapes text you could do something like....

Sub SORTIT()
    Dim i As String
    Application.ScreenUpdating = False
    ActiveSheet.Sort.SortFields.Clear
    i = ActiveSheet.Shapes("Rectangle 1").TextFrame2.TextRange.Characters.Text
        Range(Cells(i, 28), Cells(i, 52)).Sort Key1:=Cells(i, 28), Order1:=xlAscending, Header:=xlNo, _
                                                 OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
    Application.ScreenUpdating = True
End Sub

but you would have to do a separate code for each shape

 

[Herakles]

The post title suggests that a left to right sort is needed but the post text suggests right to left.

Both your answers are interesting MARK858 and I will look at the code later but I think that a right to left sort is required.

So strange that Microsoft did not think of having an xlRightToLeft sort option.

I forgot to add TonyWatsonHelp that you will need to add a worksheet named 'Helper' or whatever you want to call it but you
would need to change the code if it was named anything else.

 

[MARK858]

So strange that Microsoft did not think of having an xlRightToLeft sort option.

You just change xlAscending to xlDescending in the code.
I think it is Left to Right as he states "
(first alphabetically to last)".