Guraknugen
New Member
- Joined
- Mar 15, 2017
- Messages
- 36
Let's say I have a named range like this:
Name: "MyRange"
Range address: "A2:C101"
Sheet name: "MySheet"
This is the only way so far that I could reference that name:
So it works, but since Excel already knows that "MyRange" is actually "MySheet!A2:C101", Worksheets("MySheet") seems a little bit redundant.
I suppose there's a better way, but so far the few things I tried has failed, such as:
For reference, this is very easy in LibreOffice Calc:
vba]oRange = ThisComponent.NamedRanges.getByName("MyRange").getReferredCells()
No need to tell it what sheet it's on, it's already in the range name definition, just like in Excel.
Name: "MyRange"
Range address: "A2:C101"
Sheet name: "MySheet"
This is the only way so far that I could reference that name:
VBA Code:
Dim oRange As Range
Set oRange = Worksheets("MySheet").Range("MyRange")
So it works, but since Excel already knows that "MyRange" is actually "MySheet!A2:C101", Worksheets("MySheet") seems a little bit redundant.
I suppose there's a better way, but so far the few things I tried has failed, such as:
VBA Code:
Set oRange = Range("MyRange") ' Yields a runtime error no. 1004.
Set oRange = ThisWorkbook.Names("MyRange").RefersToRange ' 1004 again.
For reference, this is very easy in LibreOffice Calc:
vba]oRange = ThisComponent.NamedRanges.getByName("MyRange").getReferredCells()
No need to tell it what sheet it's on, it's already in the range name definition, just like in Excel.