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

Question

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'?

Thanks Chris. But I am getting Compile Error as Worng numebr of arguments or invalid property assignment. I want the code to return range 'OUTPUT!$F$44:$I$60' which is assigned to named range 'BT_GATE1'. — Vidish Soman
– Vidish Soman, Commented Oct 12, 2020 at 5:14
Hi Siddharth, I think that will assign values with in 'OUTPUT!$F$44:$I$60' to the range. I think I want some function which will return a string as "OUTPUT!$F$44:$I$60" which I can use further. — Vidish Soman
– Vidish Soman, Commented Oct 12, 2020 at 5:46
I didn't notice this first time, I think it's Set rng = ThisWorkbook.Names("BT_GATE1").RefersToRange with an s — chris neilsen
– chris neilsen, Commented Oct 12, 2020 at 5:51

Siddharth Rout · Accepted Answer · 2020-10-12 05:33:15Z

0

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

answered Oct 12, 2020 at 5:33

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vidish Soman Over a year ago

Hi Siddharth Yes It is working. I mean at least there is no compile error. My chart is moving but not as I want to. I think I am missing something. Let me know if we can chat in person so that I can share entire code and the output desired.

Siddharth Rout Over a year ago

Let me know if we can chat in person so that I can share entire code and the output desired. – Vidish Soman 1 hour ago If you are looking for free consultation then I would recommend posting your questions here so that not only I but others can also chip in and help you out. :) If you are looking for paid consultation then please feel free to click THIS

Vidish Soman Over a year ago

Thanks you Siddharth. Solution you provided worked. I could also move pie charts as per criteria within specified range with below code

Dim Rng As Range Dim ChtObj As ChartObject  Set Rng = ThisWorkbook.Name("BT_GATE1").RefersTo Debug.Print Rng.Address     Debug.Print Rng.Top     Debug.Print Rng.Parent.Name     Set ChtObj = ActiveChart.Parent     ChtObj.Top = Rng.Top     ChtObj.Left = Rng.Left

But the charts overlap at same point. Is there any way I can place charts randomly with the range?

Siddharth Rout Over a year ago

Please post a new question with the link to this question with more details and screenshots if possible and I or others will definitely reply there :)

Siddharth Rout Over a year ago

Please post a new question with the link to this question with more details and screenshots if possible and I or others will definitely reply there :)

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related