CODEWITHSUNDEEP
arraysvba
user816845
user816845

Reputation:

storing row numbers only in array using VBA

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.

Upvotes: 1

Views: 4382

Answers (2)

JMax
JMax

Reputation: 26591

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

Upvotes: 1

Reafidy
Reafidy

Reputation: 8441

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

Upvotes: 1

Related Questions