CODEWITHSUNDEEP
vbaexcel
WSC
WSC

Reputation: 992

Selecting range of data on hidden sheet without .Activate

I'm populating a combobox list via a hidden worksheet ("Data") where I have a few lists stored.

I currently select the range as below:

Dim arr() As Variant

Worksheets("Data").Activate

arr = Range("D2", Range("D2").End(xlDown).End(xlToRight))

cboBox.List = arr

However, this obviously activates the hidden sheet "Data". How can I select that same range without selecting/showing the hidden sheet?

I tried using variants of...

arr = Sheets("Data").Range("D2", Range("D2").End(xlDown).End(xlToRight))

... to avoid having to activate the sheet, but that doesn't work.

I think it's to do with calling the "D2" range twice for xlDown then xlToRight but I can't work out what the correct syntax needs to be.

Many thanks.

EDIT: YowE3K's suggestion worked:

arr = Sheets("Data").Range("D2", Sheets("Data").Range("D2").End(xlDown).End(xlToRight))

Upvotes: 0

Views: 2665

Answers (1)

SJR
SJR

Reputation: 23081

You could shorten that to 

arr = Worksheets("Data").Range("D2").currentregion

or do it thus

with Worksheets("Data")
    arr = .Range("D2", .Range("D2").End(xlDown).End(xlToRight))
end with

Upvotes: 2

Related Questions