Sorting 1,000s of rows easily.

lawnphysics · #1 01-14-2016, 01:30 PM

Hello, I need some help. I have 1,700 rows of data with 9 columns of data each.

I need to be able to sort the data in ascending order. I also want excel to keep the sort once saved and closed.

A data example is below:

Row 1: 2789 1003 3456 7867 100 1009 989 2003

Row 2: 7896 2098 7635 99 9897 61 897 4

Row 3.....

As an example column 2 would start with 989 and end in column 8 as 7867.

This goes on for 1700 rows. I have more data coming on Monday that will add another 2000+ rows. The only way I can figure out how to do it is manually. I have no idea how to use macros so if you give me a macro please explain to me how to use it. There is some coding out there for VBA that claims to do what I want it to do, but I have no idea how to insert it or use it. I have figured out how to get into the developer tab.

Thanks in Advance!

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