Sorting of Multiple Columns

sham.shah · May 5th 2020

I have a sheet with four columns that i want sorted from. I have recorded a macro to do it, which works fine for the 4 columns.

How do I do that same thing for N columns

Code

Sub Macro2()
'
' Macro2 Macro
'

'
    ActiveWorkbook.Worksheets("CT_Calc (2)").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CT_Calc (2)").Sort.SortFields.Add Key:=Range( _
        "B2:B706"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("CT_Calc (2)").Sort.SortFields.Add Key:=Range( _
        "C2:C706"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("CT_Calc (2)").Sort.SortFields.Add Key:=Range( _
        "D2:D706"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("CT_Calc (2)").Sort.SortFields.Add Key:=Range( _
        "E2:E706"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CT_Calc (2)").Sort
        .SetRange Range("A1:E706")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Display More

**royUK** · May 5th 2020

Try this

Code

Sub Macro2()
'
' Macro2 Macro
'
Dim rRng As Range
Dim iX As Integer
'
With Worksheets("CT_Calc")
Set rRng = .Range("A1").CurrentRegion
.Sort.SortFields.Clear
For iX = 2 To rRng.Columns.Count
.Sort.SortFields.Add Key:= _
rRng.Columns(iX), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
Next iX
With .Sort
.SetRange Range("A1:E706")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

Display More

I've looked at the rest of your code and it needs tidying up.

Selecting is not necessary and will slow the code down.
It's not clear what sheet the code should be working on. You should explicitly define the range including sheet name
You haven't declared your variables.
If the sheet exists that you are adding then the code will fail.

dangelor · May 5th 2020

See my reply to your earlier thread...

Sorting of Multiple Columns

Participate now!

Share

Tags