Return Column Reference from a Named Range

RelearningExcel

New Member
Joined
Jun 29, 2012
Messages
14
Hello all,

Using Name Manager I have defined a dynamic range that references a particular column. The named range grows/shrinks as data is entered into the column, and the data can be alpha or numeric. Also, if a blank cell appears in the dataset, the height of the named range is unaffected.

The user is allowed to edit the list but I also update the list with VBA as follows

Range("MyDynamicRange")(IndexVal)= "Test Value"

where IndexVal represents the row that requires updating.

From the immediate window the following VBA returns the appropriate reference for range MyDynamicRange
?activeworkbook.Names("MyDynamicRange").RefersTo

gives

=OFFSET('Criteria Lists'!$B$2,0,0,MAX(IFERROR(MATCH("*",'Criteria Lists'!$B:$B,-1),0),IFERROR(MATCH(9.99999999999999E+307,'Criteria Lists'!$B:$B,1),0))-1,1)


Also, it is legitimate for the range to be empty.
In this case Range("MyDynamicRange")(IndexVal)= "Test Value" will return err 1004.

So in the case where my named range is clear and I want to populate the first entry I have to explicitly populate cell $B$2.
After that the Named Range has data and the code will work.

I don't want to hard code the reference "$B$2" in my VBA as in
Range("$B$2")="First Entry"

So finally, here's the question....

Is it possible to return the column reference of an empty dynamic range without parsing the return value of the RefersTo property of the Names object

I've tried
?activeworkbook.Names("MyDynamicRange").RefersToRange.Address

which is ok if the range has data but returns err 1004 if it is blank

Sorry if my explanation is a bit long winded and thanks in advance,

Bill
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi Bill

Since the range doesn't exist until it contains a value I believe you will be stuck with parsing the formula. Other options could include a lookup table in an excel range of range name and starting cell as columns (although this is static, in an excel sheet/range it would be easier to update than in the code itself). The code would then just need to refer to the table.
 
Upvote 0
Is it possible to return the column reference of an empty dynamic range without parsing the return value of the RefersTo property of the Names object
Bill

I don't think so. as you have seen the RefersToRange property fails if the Name doesn't refer to a range. You could include the header in your definition and offset by 1 row when updating.
 
Upvote 0
Thanks for the suggestions guys.

Its not that difficult to parse I just wondered if there was a more elegant way.

The thing is Excel does keep track of the columns because if I SHIFT+mouse and drag the column to somewhere else the formula updates with the relevant column. I'm assuming its a hidden object.

Anyway, thanks once again :)
 
Upvote 0
The thing is Excel does keep track of the columns because if I SHIFT+mouse and drag the column to somewhere else the formula updates with the relevant column. I'm assuming its a hidden object.

Excel updates the formula just like formulas on a worksheet. It doesn't have to evaluate it.
 
Upvote 0
Thanks for that Andrew.

I have another question that is related but not sure if it needs a separate thread...

When the MyDynamicRange (above) has data in it I would have assumed that the sheet reference was implicit from its definition.

However, the following code:

Set rngAllocatedList = Range("MyDynamicRange")
rngAllocatedList.ClearContents

returns 1004.

What I have to do instead is
Set rngAllocatedList = Worksheets("Criteria Lists").Range("MyDynamicRange")
rngAllocatedList.ClearContents

Then the range will clear.

This is confusing me since if I use the Name Manager to test the range - it always points to the correct range on the sheet :confused:

regards,

Bill

(Info Update)
Maybe this is related
I have noticed that if I update the range $B$2 without a specifying the sheet then this now updates a different sheet.

ActiveWorksbook.Worksheets("Criteria Lists").Activate

has no effect before the explicit reference.
I wish I knew what I was doing !!
 
Last edited:
Upvote 0
This worked for me in Excel 2003 even if another worksheet was active:

Code:
Sub T()
    Dim rngAllocatedList As Range
    Set rngAllocatedList = Range("MyDynamicRange")
    rngAllocatedList.ClearContents
End Sub

Are you sure that the name is a workbook name rather than a worksheet name?
 
Upvote 0
I've checked the Name scope and it's definitely "Workbook".

The thing is this used to work as I expected but when I was mucking about in the immediate window I think I changed another property by accident. I will try deleting and re-adding the Range Name in Name Manager.

I am correct in thinking this is strange behavior tho?

Thanks for the swift reply btw :)
Bill
 
Upvote 0
Well I tried deleting and re-adding but still got the same problem. So I'll have to live with my workaround but it still confuses me.

Thanks for the input
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top