I was just wondering if there is a short and simple way to store the row number of cell location into an array. I have only been to store the entire cell location using the '.Address' vba function but I don't want the columns referenced so I can maniuplate the arrays later on in my sub.
Add a comment
|
2 Answers
If you want to get the row of a range, you can use the row property :
Dim myCell as Range
Dim myRow as Long
myRow = myCell.row
You can, of course, store the row in an array.
By the way, here are some tips about rows and columns : http://www.exceltip.com/excel_tips/Cells,_Ranges,_Rows,_and_Columns_in_VBA/204.html
Regards,
Max
4 Comments
JMax
the
rows property contains every row of the range. Hence, rows.count is the number of rows of your range. If you want to get the index of each row in an array, i think you will have to loop over the rows (for each... next)
Charles Williams
Max,In VBA you should always use Long rather than Integer when referring to rows to prevent Overflow. VBA integer only holds up to 32768.
JMax
@Charles Williams: thanks for this tip. i'll edit my answer :)
Try:
Sub HTH()
Dim rCell As Range
Dim vMyArray() As Variant
Dim iLoop As Integer
For Each rCell In Range("A1:A10")
ReDim Preserve vMyArray(iLoop)
vMyArray(iLoop) = rCell.Row
iLoop = illop + 1
Next rCell
End Sub
Comments
