ok - spent over an hour on this so far with no luck ...(including google
newgroups)

I want to get the row number for each row in a range....so if a range has 30
rows starting at B3 then I want 3,4,5.....33 returned.

Is this possible? Probably easy but I'm missing it....

If this was access I would use the "for each row in myrange.rows"
collection and look at the property values to get this but I noticed excel
does not have a 'property' nor 'row' object.

Thanks

--
kent eilers

Hi

one way

Firstrow = Range("b3:b33").Cells(1).Row
'This give you the row number of the first cell in the range

Rcount = Range("b3:b33").Rows.Count + Firstrow
'This give you the last row in the range

Sub test()
Firstrow = Range("b3:b33").Cells(1).Row
LRow = Range("b3:b33").Rows.Count + Firstrow

For Rownumber = Firstrow To LRow Step 1
MsgBox Rownumber
'MsgBox Cells(Rownumber, "A").Value
Next
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Kent Eilers" wrote in message ...

ok - spent over an hour on this so far with no luck ...(including google
newgroups)

I want to get the row number for each row in a range....so if a range has 30
rows starting at B3 then I want 3,4,5.....33 returned.

Is this possible? Probably easy but I'm missing it....

If this was access I would use the "for each row in myrange.rows"
collection and look at the property values to get this but I noticed excel
does not have a 'property' nor 'row' object.

Thanks

--
kent eilers

woot !

thanks
))

"Ron de Bruin" wrote in message
...
Hi

one way

Firstrow = Range("b3:b33").Cells(1).Row
'This give you the row number of the first cell in the range

Rcount = Range("b3:b33").Rows.Count + Firstrow
'This give you the last row in the range

Sub test()
Firstrow = Range("b3:b33").Cells(1).Row
LRow = Range("b3:b33").Rows.Count + Firstrow

For Rownumber = Firstrow To LRow Step 1
MsgBox Rownumber
'MsgBox Cells(Rownumber, "A").Value
Next
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"Kent Eilers" wrote in message
...

ok - spent over an hour on this so far with no luck ...(including google
newgroups)

I want to get the row number for each row in a range....so if a range
has 30
rows starting at B3 then I want 3,4,5.....33 returned.

Is this possible? Probably easy but I'm missing it....

If this was access I would use the "for each row in myrange.rows"
collection and look at the property values to get this but I noticed
excel
does not have a 'property' nor 'row' object.

Thanks

--
kent eilers

The following will give you an array that holds the number of each row.
Remember that Arrays start with element #0 by default:

a = Selection.Rows.Count
b = Selection.Row
ReDim array1(a)
For n = 0 To a - 1
array1(n) = b + n
Next n

- Piku

--
Message posted from http://www.ExcelForum.com

Well, it isn't Access but how about:

Sub test3000()
Dim myRange As Range, row
Set myRange = Range("b3:b32")
For Each row In myRange.Rows
Debug.Print row.Row
Next
End Sub

And for what it's worth, with 30 rows starting at b3 you want
3,4,5.....32 returned; and I wouldn't really use a variable named row,
the same as the name of an Excel Property.

Alan Beban

Kent Eilers wrote:
ok - spent over an hour on this so far with no luck ...(including google
newgroups)

I want to get the row number for each row in a range....so if a range has 30
rows starting at B3 then I want 3,4,5.....33 returned.

Is this possible? Probably easy but I'm missing it....

If this was access I would use the "for each row in myrange.rows"
collection and look at the property values to get this but I noticed excel
does not have a 'property' nor 'row' object.

Thanks

Thanks everyone for the help.

))

"Kent Eilers" wrote in message
...

ok - spent over an hour on this so far with no luck ...(including google
newgroups)

I want to get the row number for each row in a range....so if a range has
30
rows starting at B3 then I want 3,4,5.....33 returned.

Is this possible? Probably easy but I'm missing it....

If this was access I would use the "for each row in myrange.rows"
collection and look at the property values to get this but I noticed excel
does not have a 'property' nor 'row' object.

Thanks

--
kent eilers