Named ranges in Excel through VBA commands

[daggers]

Hi,

I am using the following code to calculate the size of a data list in Excel, and name the range of cells...

end_row = Range("A65000&quot.End(xlUp).Row
Sheets(9).Range(Cells(2, 1), Cells(end_row, 9)).Select
Sheets(9).Names.Add Name:="test", RefersToR1C1:="='ECP FX'!R2C1:R6C9"

my problem is: when the size of the list changes, how do I overwrite the named range 'test'? is it possible to delete the named range before renaming it? also, how is the code " RefersToR1C1:="='ECP FX'!R2C1:R6C9" " changed to relative references from absolute cell references (to reflect the value 'end_row')??

TIA!

Ross

 

[xlbo]

This should work - just use the RefersTo property rather than the RefersToR1C1 property

end_row = Range("A65000&quot.End(xlUp).Row

with Sheets(9).Names.("test&quot
.RefersTo:="='ECP FX'!A2:I" & end_row
end with


Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[SkipVought]

Hi,

You can dynamically change the ranges of named ranges using this code in the Worksheet_Change event. It assumes that your table has unique names in ROW 1 starting in COLUMN A, and that ALL the data is contiguous...

Sub CreateNamesTop()
    ActiveSheet.Cells(1, 1).CurrentRegion.CreateNames _
        Top:=True, _
        Left:=False, _
        Bottom:=False, _
        Right:=False
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com