#1
|
|||
|
|||
Sorting 1,000s of rows easily.
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! |
#2
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
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 |