Copy the new column then paste back using Paste Special and select value to
replace the formula with a number. Then sort.

"NDBC" wrote:

Thanks Joel

Now I know the max number of columns. So it's 100 rows by (in this case) 7
columns (including name and rider number). How to I sort that with code.


"Joel" wrote:

Put in an auxilary column the number of columns with data. If lap 1 starts
in column J then in row 5

=countA(J5:P5)

Then copy the formula down the column. Now sort this new column in
descending order. The new column will tell you the number of laps completed
since countA is the count of non blank cells.

"NDBC" wrote:

I think I've left the hardest question to last. See data below
H I J K L M
N O P.....
Name No lap1 lap2 lap3 lap4 lap5
lap6........

5 Name / Name 100 0:00:08
6 Name / Name 101 0:00:10 0:00:18
7 Name / Name 102 0:00:11 0:00:19 0:00:19 0:00:25 0:00:27
8 Name / Name 103 0:00:13 0:00:21 0:00:21 0:00:27 0:00:31
9 Name / Name 104 0:00:16 0:00:24 0:00:24 0:00:29 0:00:29 0:00:33
10 105
11 106
.
.
.

I need to sort the riders to determine places. The H,I.... and 5,6... are
cell references (and do not change) if that helps. The number of laps any one
rider may have completed in a race varies from 0 to unlimited. Riders are
ranked by number of laps completed and then finish time on their last lap if
number of laps is equal.

There are 2 ways to determine the number of rows for sorting that I can see.
1) determine the number of rider names till the first blank
2) just sort every rider number anyway even if unused. The maximum rider
number is 199 (row 104).

My hassle is determining the max number of columns for the range and then
sorting the rows based on what could realistically be 30 columns of times.

Thanks

I see two problems . One, I am trying to write code for a command button to
do this so that inexperienced users don't have to do anything but click the
button. The second is the method below doesn't sort the rows with the same
number of laps. There could be 5 competitors with 6 laps, 10 with 5 laps 4
with 4 laps etc.





"Joel" wrote:

Copy the new column then paste back using Paste Special and select value to
replace the formula with a number. Then sort.

"NDBC" wrote:

Thanks Joel

Now I know the max number of columns. So it's 100 rows by (in this case) 7
columns (including name and rider number). How to I sort that with code.


"Joel" wrote:

Put in an auxilary column the number of columns with data. If lap 1 starts
in column J then in row 5

=countA(J5:P5)

Then copy the formula down the column. Now sort this new column in
descending order. The new column will tell you the number of laps completed
since countA is the count of non blank cells.

"NDBC" wrote:

I think I've left the hardest question to last. See data below
H I J K L M
N O P.....
Name No lap1 lap2 lap3 lap4 lap5
lap6........

5 Name / Name 100 0:00:08
6 Name / Name 101 0:00:10 0:00:18
7 Name / Name 102 0:00:11 0:00:19 0:00:19 0:00:25 0:00:27
8 Name / Name 103 0:00:13 0:00:21 0:00:21 0:00:27 0:00:31
9 Name / Name 104 0:00:16 0:00:24 0:00:24 0:00:29 0:00:29 0:00:33
10 105
11 106
.
.
.

I need to sort the riders to determine places. The H,I.... and 5,6... are
cell references (and do not change) if that helps. The number of laps any one
rider may have completed in a race varies from 0 to unlimited. Riders are
ranked by number of laps completed and then finish time on their last lap if
number of laps is equal.

There are 2 ways to determine the number of rows for sorting that I can see.
1) determine the number of rider names till the first blank
2) just sort every rider number anyway even if unused. The maximum rider
number is 199 (row 104).

My hassle is determining the max number of columns for the range and then
sorting the rows based on what could realistically be 30 columns of times.

Thanks

Sorry I wasn't clearer about wanting to do this with code. By sorting rows
with the same number of laps I mean sort rows with same number of laps in
ascending order based on the time it was when they finished their last lap.
(ie. fastest to slowest in each lap)

Thanks again for your help.

"NDBC" wrote:

I see two problems . One, I am trying to write code for a command button to
do this so that inexperienced users don't have to do anything but click the
button. The second is the method below doesn't sort the rows with the same
number of laps. There could be 5 competitors with 6 laps, 10 with 5 laps 4
with 4 laps etc.





"Joel" wrote:

Copy the new column then paste back using Paste Special and select value to
replace the formula with a number. Then sort.

"NDBC" wrote:

Thanks Joel

Now I know the max number of columns. So it's 100 rows by (in this case) 7
columns (including name and rider number). How to I sort that with code.


"Joel" wrote:

Put in an auxilary column the number of columns with data. If lap 1 starts
in column J then in row 5

=countA(J5:P5)

Then copy the formula down the column. Now sort this new column in
descending order. The new column will tell you the number of laps completed
since countA is the count of non blank cells.

"NDBC" wrote:

I think I've left the hardest question to last. See data below
H I J K L M
N O P.....
Name No lap1 lap2 lap3 lap4 lap5
lap6........

5 Name / Name 100 0:00:08
6 Name / Name 101 0:00:10 0:00:18
7 Name / Name 102 0:00:11 0:00:19 0:00:19 0:00:25 0:00:27
8 Name / Name 103 0:00:13 0:00:21 0:00:21 0:00:27 0:00:31
9 Name / Name 104 0:00:16 0:00:24 0:00:24 0:00:29 0:00:29 0:00:33
10 105
11 106
.
.
.

I need to sort the riders to determine places. The H,I.... and 5,6... are
cell references (and do not change) if that helps. The number of laps any one
rider may have completed in a race varies from 0 to unlimited. Riders are
ranked by number of laps completed and then finish time on their last lap if
number of laps is equal.

There are 2 ways to determine the number of rows for sorting that I can see.
1) determine the number of rider names till the first blank
2) just sort every rider number anyway even if unused. The maximum rider
number is 199 (row 104).

My hassle is determining the max number of columns for the range and then
sorting the rows based on what could realistically be 30 columns of times.

Thanks