Sorting 1,000s of rows easily.

macropod · #2 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
how to easily drop autotext into document	Kendel	Word	2	02-08-2015 09:50 AM
Two slides merge or view easily easy they are linked to each other	Rajeev Saini	PowerPoint	1	08-22-2014 06:56 AM
Is there a function to easily and quickly highlight?	Verbum	Word	3	02-05-2013 10:09 AM
How to add 1,000s of email accounts	nighttraindb	Outlook	1	01-19-2012 10:14 AM
Want boss to email me at two addresses--can I set this up easily?	jessica19087	Outlook	1	08-26-2010 11:37 AM