Counting rows within a dynamic range

[Tom Malinski]

A Newbie question
I am using named dynamic ranges utilizing the OFFSET formula for charting. I
have added a new column also with a dynamic range named RideDays. I would
simply like to add a sequential number 1,2,3,4, etc...to the 1st cell in each
row within the RideDay range.

RideDays Distance Time AveMPH
1 12
2 20
3 15
4 40

Thank you
Tom...

 

[Don Guillett]

MsgBox Range("myrangename").Rows.Count

 

[Tom Malinski]

Thanks Don, but where do I put this

MsgBox Range("myrangename").Rows.Count

 

[Don Guillett]

Try this where col b is the longest column
Sub addridedaysnums()
x = Cells(Rows.Count, "b").End(xlUp).Row
For i = 2 To x
Cells(i, "a") = i - 1
Next
End Sub

 

[Tom Malinski]

Don, I appreciate your help, but when I copy your example to my code page it
doesnt do anything, no numbers appear anywhere on my data sheet. Is there
something I'm not doing that I should be?
Thanks again

 

[Tom Ogilvy]

Assume the 1 you show is A2 (but A2 is actually empty).

then in A2 put a 1 and in A3 put a 2
A2: 1
A3: 2

select A2:A3 and click on the little black square in the lower right corner
of the outline. Hold down the mouse and drag down the column. this will
fill the cells with sequential numbers.

 

[Daniel.M]

Tom,

Dynamic range means you have something like:
DynRng = OFFSET(startcell,0,0,Count(...),1)


1.Define a new name with the Count(...) in there, as in:
MyCount = Count(...)

2.Redefine DynRng based on MyCount (instead of redoing the calc withing OFFSET)
DynRng = OFFSET(startcell,0,0,MyCount,1)

3.Define a NewDynColumn Name (so press Ctrl-F3) to have 1 to MyCount:
NewDynColumn = Row(INDIRECT("1:"&MyCount))

4.Use the NewDynColumn in your chart (the same way you did with DynRng
originaly).

Regards,

Daniel M.

 

[Don Guillett]

Using the example you gave to fill col A based on col B