Sorting data takes a long time for large amount of data. any shortcuts

[MeghaJ]

Dear Friends,

Please find the code for sorting data:

Sub SortData()
Dim nLastRow, Rownum As Integer
nLastRow = 0
Rownum = 0
Worksheets("Sheet2").Activate
Sheets("Sheet2").Select
nLastRow = Range("A" & Rows.Count).End(xlUp).Row

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("D:D") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("E:E") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
    .SetRange Rows("1:" & nLastRow)
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub

I have around 50,000 rows of data and need to sort this data in 3 levels.
This takes a lot of time.

Please tell me how this can be achieved in a shorter duration.

Thanks
megha

 

[Asala42]

Does manually sorting by those levels take a similarly long amount of time? In other words, is it the sorting that's taking a long time or is it something from the code?

Here's a standard set of sorting code that I use very frequently (on large tables similar to yours, usually 80K records by 160 columns). Feel free to use/adapt if you suspect your code is causing the slow down. It assumes your table includes column A, titles in row 1.

'Sort my data dammit
 ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("E1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

If its' the sort itself that's causing the problem: It's probably being caused by formulas in your data range. One thing you can do, is test clearing the formulas (via copy/paste as values, perhaps leaving the first formula row for convenience later) and then trying the sort. Often formulas will cause slowdown as the sort triggers a recalculate.