Independently sort multiple columns in excel

Question

I'm trying to sort a large number of lists from A to Z. But if I sort column A, I don't want all other columns to be rearranged. I need all these lists to indivually be sorts alphabetically. I know it's possible to do one by one but I have 278 columns. Is there a way do automate it?

Solution 1

If there are no blank cells in any of the columns' data then the following is a slightly different approach which doesn't assume a maximum for the number of rows.

Sub SortIndividualJR()
    Dim rngFirstRow As Range
    Dim rng As Range
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    Set rngFirstRow = ws.Range("A1:JR1")
    For Each rng In rngFirstRow
        With ws.Sort
            .SortFields.Clear
            .SortFields.Add Key:=rng, Order:=xlAscending
            'assuming there are no blank cells..
            .SetRange ws.Range(rng, rng.End(xlDown))
            .Header = xlYes
            .MatchCase = False
            .Apply
        End With
    Next rng
    Application.ScreenUpdating = True
End Sub

There is nothing wrong with user1281385's code; as I say, this is just an alternative.

Added If there are some blanks then modify the above code to use .SetRange ws.Range(rng, rng.Range("A1000").End(xlUp)), changing 1000 to whatever you expect will be greater than the maximum number of data-rows.

Solution 2

Select the column you want to sort then press "sort and filter" a box will come up saying

Expand selection
continue with current selection

Choose continue with current selection

Then choose how you want it sorted. Repeat for each column you want to sort.

Expand selection is the default option

Macro

Dim oneRange as Range 
Dim aCell as Range 
For I = 1 to 278
    Set oneRange = Range("r1c" & I & ":r1000c" & I) 
    Set aCell = Range("r1c" & I)
    oneRange.Sort Key1:=aCell, Order1:=xlAscending, Header:=xlYes
Next I

Should work