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

Grouping Sequential Numbers
 

Without seeing your data and such it is a little hard to comment. the only
suggestions that I might have to speed things up would be to turn calculation
off at the beginning and on again at the end. Other than that you are
deleting essentially one record at a time which is slow. Try to create a
large range (union is a number of small ranges) encompassing all of the data
you want to delete and then just perform one big delete at the end.
--
HTH...

Jim Thomlinson


"crazybass2" wrote:

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

Grouping Sequential Numbers
 

The process you are trying to do requires, regardless of methodology, that
each cell in a column be evaluated. If you are using almost every row, as
you mention, this will take a very very long time.

Can you perhaps explains why you are trying to do this... people may have
some creative ideas to reach you ultimate end-state.

"Jim Thomlinson" wrote:

Without seeing your data and such it is a little hard to comment. the only
suggestions that I might have to speed things up would be to turn calculation
off at the beginning and on again at the end. Other than that you are
deleting essentially one record at a time which is slow. Try to create a
large range (union is a number of small ranges) encompassing all of the data
you want to delete and then just perform one big delete at the end.
--
HTH...

Jim Thomlinson


"crazybass2" wrote:

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

Grouping Sequential Numbers
 

Jim,

Thanks for the reply. I do have both ScreenUpdating and Calculations off
throughout the entire code. I will try as you suggested to use the Union or
similar type function.

Mike

"Jim Thomlinson" wrote:

Without seeing your data and such it is a little hard to comment. the only
suggestions that I might have to speed things up would be to turn calculation
off at the beginning and on again at the end. Other than that you are
deleting essentially one record at a time which is slow. Try to create a
large range (union is a number of small ranges) encompassing all of the data
you want to delete and then just perform one big delete at the end.
--
HTH...

Jim Thomlinson


"crazybass2" wrote:

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