Grouping Sequential Numbers
I have a sub that groups lists of numbers under their headers (each header in
a separate column) and then sorts each column from lowest to highest. Some
columns have 60,000+ values so it is a fairly large group of data. Most of
these numbers are sequential, but there are places where the sequense skips a
few. What I'm trying to do is group all the sequential numbers into a text
range thereby deceasing the number of rows used in each column. The code
below does the job, but because it loops through each line, it takes a long
time to complete. I'm wondering if there is a faster method of doing this.
Sample data (before code):
1
2
3
4
5
9
10
11
13
18
19
20
Sample data (after code) - Note: the range lists are TEXT strings now, not
numbers:
1 to 5
9 to 11
13
18 to 20
Code is as follows:
Do While rng.Column <= Sheet2.UsedRange.Columns.Count
i = 1
j = 0
start = rng.Value
nextln = rng.Offset(1, 0).Value
Do While Not nextln = ""
If nextln - start = i Then
i = i + 1
rng.Value = start & " to " & nextln
Cells(rng.Row + 1, rng.Column).Delete (xlShiftUp)
Else *****SEE NOTE BELOW ******
Set rng = rng.Offset(1, 0)
i = 1
start = rng.Value
End If
nextln = rng.Offset(1, 0).Value
Loop
Set rng = Sheet2.Cells(2, rng.Column + 1)
Loop
Any help or suggestion is greatly appreciated!
Thanks,
Mike
|