excel named ranges issue Excel 2013

[Excel2006]

Hi guys

Please can you help me with this code. I am trying to update named ranges via excel vba

range name = "Building-Name" already exists.

It had less than 100 records and this had increased to over 100.

I want the macro to auto update the named range every time I add extra records.
Please would you help me. I get an error code when I try the code below.

Public Sub Example()
Range("g1:g" & Range("g1").End(xlDown).Row).Name = "Building-Name"
End Sub

Regards

excel2006

 

[njimack]

Surely the better way would be to use a table or dynamic ranges - http://contextures.com/xlNames01.html#Dynamic

 

[Excel2006]

Surely the better way would be to use a table or dynamic ranges - http://contextures.com/xlNames01.html#Dynamic

CHeers, Neil, but is there a way to do this in VBA as my spreadsheet contains a lot of VBA code

 

[peterhinton]

Use =OFFSET($G$1,0,0,COUNTA($G:$G),1) for the range, it will update automatically everytime a record is added

 

[njimack]

CHeers, Neil, but is there a way to do this in VBA as my spreadsheet contains a lot of VBA code

There's no need - set up the dynamic named ranges and they will automatically adjust as data is added / deleted. You can refer to the ranges in your code and don't need to worry about adjusting the dimensions of the ranges.