# Excel/VB Gods, help me again

**URL:** <https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870>\
**Category:** Factual Questions\
**Created:** [March 26, 2009, 2:07pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870 "2009-03-26T14:07:53Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\
**Post date:** [March 26, 2009, 2:07pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870/1 "2009-03-26T14:07:53Z")

</div>

I’m trying to define a range of cells as a name during a macro.

The problem is, the range may change from one use to the next, but the macro definition always uses the range that was selected the time the macro was recorded.

This is the code that is generated:  
Range(“A2”).Select  
Range(Selection, Selection.End(xlDown)).Select  
ActiveWorkbook.Names.Add Name:=“inscbp”, RefersToR1C1:="=Lists!R2C1:R6C1"

The macro selects the correct range of cells (from a specified cell to the bottom contiguous cell in use), but it doesn’t use the active selection in the name definition. I need some replacement code for RefersToR1C1:="=Lists!R2C1:R6C1" to point to the active selection.

---

<div class="post-metadata">

**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\
**Post date:** [March 26, 2009, 2:39pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870/2 "2009-03-26T14:39:39Z")

</div>

I hate to be obvious, but have you tried “RefersTo:= Selection”?

---

<div class="post-metadata">

**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\
**Post date:** [March 26, 2009, 2:51pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870/3 "2009-03-26T14:51:57Z")

</div>

:smack::smack::smack:

Oh, Christ on a crutch. I was trying “selection” and slection.xldown as replacements for the “r” variable, I never thought of just chucking the statement wholesale.

You are truly a god.

---

<div class="post-metadata">

**Author:** ![KneadToKnow](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/kneadtoknow/32/3999_2.png) [@KneadToKnow](https://boards.straightdope.com/u/KneadToKnow)\
**Post date:** [March 26, 2009, 2:58pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870/4 "2009-03-26T14:58:14Z")

</div>

Happy to help. I did have to look up the arguments for Names.Add to make sure there was one that was just “RefersTo,” so it’s not like I popped that out from memory. 🙂

---

<div class="post-metadata">

**Author:** ![AllShookDown](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@AllShookDown](https://boards.straightdope.com/u/AllShookDown)\
**Post date:** [March 27, 2009, 6:39pm UTC](https://boards.straightdope.com/t/excel-vb-gods-help-me-again/490870/5 "2009-03-27T18:39:25Z")

</div>

You could also try using a dynamic range name, instructions [here.](http://www.ozgrid.com/Excel/DynamicRanges.htm) Then you don’t have to deal with redefining the range in the macro.

When I use them they seem to always add a few extra rows at the bottom of the range, which isn’t a problem unless I’m doing a pivot table off the data. And then I just deselect “blanks” in the drop-downs. I have had a case where a pivot table refused to run off a dynamic range at all, but only once. Never did figure out why. Anyway, I like them for my monthly reports so I can update the data and not have to ever redefine the range.