Named Range

[How_Do_I]

I was to name A31 and A32 x and y in my workbook...

If I delete or add rows and columns I always want x and y to refer to A31 and A32.

Can this be done please and if so how?

 

[Sektor]

Sub CoolVBA()
    Names.Add Name:="x", RefersTo:="=Sheet1!$A$31"
    Names.Add Name:="y", RefersTo:="=Sheet1!$A$32"
End Sub

 

[How_Do_I]

Thanks so that have to be done with VBA and can't be done in names?

 

[Sektor]

Screenshot

 

[Misca]

Try using the OFFSET-function:
=OFFSET(Sheet1!$A$1,30,0)
should work as long as you don't delete A1. If that is not enough, you're going to need a simple macro to set the ranges:

Private Sub Worksheet_Change(ByVal Target As Range)
'Paste this to the worksheet module of the sheet where you want to have the named ranges
Dim X As Range
Dim Y As Range
Application.EnableEvents = False    'Disables events - just to be sure
'The ranges on active sheet:
Set X = Range("A31")
Set Y = Range("A32")
'Adds the ranges to Names:
With ActiveWorkbook.Names
    .Add Name:="X", RefersToR1C1:=X
    .Add Name:="Y", RefersToR1C1:=Y
End With
Application.EnableEvents = True 'Turns the events back on
End Sub

 

[How_Do_I]

OK thanks all...

 

[GlennUK]

Or you could use the INDIRECT function, like:

=INDIRECT("Sheet1!A31")

 

[Peter_SSs]

Edit: Oops, sorry, didn't see Glenn's post

Thanks so that have to be done with VBA and can't be done in names?

If I have understood correctly, Sektor's code does not do what you want anyway. It defines x as referring to A31 but if rows are added or deleted it no longer refers to A31.

Secondly, I don't think you need vba like Misca has suggested to keep re-defining x.

Try this ..

1. Ensure you have deleted any existing 'x' or 'y' names.

2. Manually, (post back with your Excel version if you need precise instructions) Define name| Name: x | Refers to" =INDIRECT("A31") |OK

3. Similar for 'y' (=INDIRECT("A32") )

Now if you add/delete rows, the names should still refer to A31 and A32. Hope I have understood correctly.

 

[How_Do_I]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Thanks Glenn and Peter… I’ve opted for your solution as it makes amending my books so much easier…