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.
I hate to be obvious, but have you tried “RefersTo:= Selection”?
: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.
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. 
You could also try using a dynamic range name, instructions here. 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.