CODEWITHSUNDEEP
vbaexcel
Mark Spain
Mark Spain

Reputation: 83

Pass Upper Boundary to Array

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.

Upvotes: 3

Views: 78

Answers (3)

user4039065
user4039065

Reputation:

I generally prefer to reference the parent worksheet and define the start and stop of the range with cells.

Dim lrow As long, phonesArray as variant

with worksheets("your_worksheet's_name")
    lrow = .cells(.rows.Count, 1).End(xlUp).Row
    phonesArray = .range(.cells(1, "A"), .cells(lrow, "A")).Value2
    debug.print lbound(phonesArray, 1) & " to " & ubound(phonesArray, 1)
    debug.print lbound(phonesArray, 2) & " to " & ubound(phonesArray, 2)
end with

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

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

Upvotes: 6

Nathan_Sav
Nathan_Sav

Reputation: 8531

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

Upvotes: 4

Related Questions