sort multiple columns of a single row

[r121a947]

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 each word is 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.

This is also CROSS POSTED on the Excel guru forum.

 

[NARAYANK991]

Hi ,

You say this :

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

Why do you have this special requirement ?

Narayan

 

[r121a947]

I have a music database. One field contains codes for keywords found in the lyrics of that song. I want to be able to sort that field so I can more easily determine which keywords are found in a particular song.

 

[r121a947]

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.

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

 

[Fluff13]

How about

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

 

[r121a947]

Works perfectly.

Thank you for your expertise and assistance.

 

[Fluff13]

You're welcome & thanks for the feedback