Search code examples
excelvbasyntaxrangeworksheet

Range activation in a different worksheet

I was wondering if there is an alternative for the following code:

Dim Arr() as Variant
Arr = Range("A1:I511")

that refers to a specified worksheet. I tried the following

Dim Arr() as Variant
Arr = Range(Sheets("list").Cells(1, 1), Sheets("list").Cells(511, 9))

Is there another way to write this? I want to have something like this:

Dim Arr() as Variant
Arr = worksheets("list").range(Cells(1, 1),Cells(511, 9))
Solution

  • There's a number of other ways to write this, perhaps most simply:

    arr = Sheets("list").Range("A1:I511").Value

    But here is the problem with your attempts:

    Cells(_row_, _column_) returns a range object, which evaluates to its default property (the .Value property. So when you do:

    Range(Sheets("list").Cells(1, 1), Sheets("list").Cells(511,9))

    You're actually doing this, which will raise an error unless the unlikely circumstance that these cells contain a string value that can be interpreted as a range address like "$A$1" or "$J$5:$Q$459", etc.

    Range(Sheets("list").Cells(1, 1).Value, _ Sheets("list").Cells(511,9).Value)

    Try this instead:

    Range(Sheets("list").Cells(1, 1).Address, _ Sheets("list").Cells(511,9).Address).Value

    Or:

    With Sheets("list")
   arr = Range(.Cells(1, 1).Address .Cells(511,9).Address).Value
End With

    NB You can't activate a range on another sheet without first activating that sheet. I would advise you against doing both. There is 99% of the time no reason to "activate" anything in VBA. See this for further explanation:

    How to avoid using Select in Excel VBA macros