getting coordinates of a range

  • Thread starter Thread starter Kent Eilers
  • Start date Start date
K

Kent Eilers

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
 
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
 
woot !

thanks
))

Ron de Bruin said:
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
Click to expand...
 
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
 
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
 
Back
Top