Expanding a named range

[davey4444]

I have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.

What I'd like to create is a named range which includes this original range as well as columns a and m. Is this possible using Offset at all?

On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.

Thanks in advance.

 

[Aladin Akyurek]

I have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.

What I'd like to create is a named range which includes this original range as well as columns a and m. Is this possible using Offset at all?

On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.

Thanks in advance.

Care to post the current definition you have?

 

[jim may]

Here's an example

Sub Foo()
Dim NewRng As String
Dim rc as long
With Range("InnerRange")   'Testing your existing InnerRange Name for the current Number of rows
    rc = .Rows.Count
End With
NewRng = Range("InnerRange").Offset(-1, -1).Resize(rc + 1, 5).Address  'Get Address of desired expanded OutterRange
Range("OutterRange").Name.Delete    'Remove any existing reference to the OutterRange should it exist
ActiveWorkbook.Names.Add Name:="OutterRange", _
        RefersTo:=Sheets("Sheet1").Range(NewRng)   'Add/Create your New OutterRange RangeName
End Sub

 

[davey4444]

Thanks Jim, your code worked great. The only hiccup was where it checks to see if the name previously exists where I had an "object not defined" error but that was easily overcome.