Pass Upper Boundary to Array

Question

I want my macro to read from a list of phone numbers on another sheet, count the rows, then construct an array from A1:An...from the row count. The array will always start at A1.

Dim lrow As Variant 

lrow = Cells(Rows.Count, 1).End(xlUp).Row

Dim PhonesArray as Variant

PhonesArray = " [A1:A" & lrow & "].Value2

I'm unable to pass the upper boundary (lrow) to PhonesArray.

it should run as

PhonesArray = [A1:A**40**].Value2

The lrow variable is calculating correctly, but I'm unable to pass it into the array construction. A static range works as expected. Any assistance is greatly appreciated and apologies in advance if the issue has been addressed before. I was unable to find a solution through my search.

Answer 1

In general, it's a bad idea to refer to your cells with the [A1] type shorthand and it doesn't support being put together in a string like that. Use Range() instead and you'll have a few options:

• Range("A1:A" & lRow)
• Range("A1").Resize(lrow,1)

Given the code you've provided, I'd scrap the lrow variable and just use this:

Dim PhonesArray As Variant

With ThisWorkbook.Worksheets("Sheet1")
    PhonesArray = Range(.Range("A1"), .Cells(.Rows.Count, 1).End(xlUp)).Value2
End With

Answer 2

You require Range("a1:a" & lRow).value2 I believe