Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sequential Numbers | Excel Discussion (Misc queries) | |||
Sequential Numbers | Excel Discussion (Misc queries) | |||
sequential numbers | Excel Worksheet Functions | |||
Sequential numbers | Excel Programming | |||
sequential numbers | Excel Discussion (Misc queries) |