Results 1 to 7 of 7

Thread: sort single row/multiple columns

  1. #1
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    25
    Articles
    0
    Excel Version
    Office 365

    sort single row/multiple columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Is there a way to sort a portion of a single row over multiple columns, without having to specify a Sort by column?

    I have individual words in several columns, and I want to sort the columns alphabetically, regardless of which column the words are in.

    For example, each column from M to W has a single word. I want to sort that portion of the row so that the words are rearranged in the columns alphabetically, lowest in M, highest in W.

    Thank you.

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,699
    Articles
    0
    Excel Version
    O365
    If you select the columns and do a sort left to right that should work.

  3. #3
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    25
    Articles
    0
    Excel Version
    Office 365
    I tried that . . . No success. It still asks for a Sort by criterion.

    Thank you.

  4. #4
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    25
    Articles
    0
    Excel Version
    Office 365
    I am going to CROSS POST this on the Chandoo forum.

  5. #5
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    25
    Articles
    0
    Excel Version
    Office 365
    I did a manual sort and recorded it as a macro. The code is pasted below.

    I am unable to get the proper syntax for the range, which would be columns M thru AH for each row.

    Any help will be greatly appreciated.

    Code:
    Sub Jsort()
    '
    ' Jsort Macro
    ' Sort the individual words in the J column cell
    '
    ' Keyboard Shortcut: Ctrl+Shift+J
    '
        Dim cur As Integer
        Dim i As Integer
        ' cur = 1
        
        
        
        For i = 1 To 16
        cur = i
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=ActiveCell. _
            Range("Mcur:AHcur"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("Mcur:AHcur")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlLeftToRight
            .SortMethod = xlPinYin
            .Apply
        End With
        Next i
    End Sub

  6. #6
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    25
    Articles
    0
    Excel Version
    Office 365
    I received a solution . . .
    Code:
    Sub Jsort()
    '
    ' Jsort Macro
    ' Sort the individual words in the J column cell
    '
    ' Keyboard Shortcut: Ctrl+Shift+J
    '
        Dim i As Integer
        
        
        
        For i = 1 To 16
            With ActiveWorkbook.Worksheets("Sheet1").Sort
                .SortFields.Clear
                .SortFields.Add Key:=Range("M" & i).Resize(, 22) _
                    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                    xlSortNormal
                .SetRange Range("M" & i).Resize(, 22)
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlLeftToRight
                .SortMethod = xlPinYin
                .Apply
            End With
        Next i
    End Sub

  7. #7
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,692
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please add a link to your cross-post(s). Thanks
    Thank you Ken for this secure forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •