Microsoft Office Forums - View Single Post

macropod · 01-14-2016, 03:58 PM

You could use a macro like the following. On my laptop, it takes less than 2 seconds to sort 10,000 rows (16 seconds for 100,000 rows).

Code:

Sub DataSort()
Application.ScreenUpdating = False
Dim i As Long, Rng As Range
With ActiveWorkbook.ActiveSheet
  For i = 1 To .UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row
    Set Rng = .Range("A" & i & ":H" & i)
    With .Sort
      .SortFields.Clear
      .SortFields.Add Key:=Rng, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Rng
      .Header = xlNo
      .MatchCase = False
      .Orientation = xlLeftToRight
      .SortMethod = xlPinYin
      .Apply
    End With
    If i Mod 5000 = 0 Then DoEvents
  Next i
End With
Application.ScreenUpdating = True
End Sub

For PC macro installation & usage instructions, see: http://www.gmayor.com/installing_macro.htm. Although the article is for Word, the process for Excel is essentially the same.