Search code examples
excelvbanamed-ranges

How to get range assigned to Named Range in excel vba?

I have already defined the named ranges in my workbook. I want to use the range for placing my pie chart. I am trying to write a code which sets range to variable and move the chart to the specific location.

Dim Rng As Range
Dim ChtObj As ChartObject

Set Rng = ThisWorkbook.Name("BT_GATE1").RefersTo
Set ChtObj = ActiveChart.Parent
ChtObj.Top = Rng.Top

I think I am missing something or using a worng method. Can some one help me assigning a range to variable 'Rng'?

Solution

  • A named range is either one cell or a collection of more cells which have been given a name. Ultimately it is a range. So in your case you can directly set it to a range as shown below

    Dim Rng As Range

Set Rng = Range("BT_GATE1")

Debug.Print Rng.Address
Debug.Print Rng.Top
Debug.Print Rng.Parent.Name