offset function in defining name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BorisS - 29 May 2008 16:19 GMT

I am trying to define a name in a sheet (the name of the sheet will change
each, and I need function to insert into a macro, which will re-define this
named ranged each time a system generates a new file).

For the named range, I am using
OFFSET(A1,0,0,counta(A:A)+1,12)

because the 12 is the width of the columns I need. I would LIKE for it to
be dynamic, and figure out on its own how many columns to set as width.

When I try something like 'count(3:3)' (which is the row that helps define
width), I am not getting it to work

Any suggestions? Thx much.

Signature

Boris

Reply to this Message

Bernie Deitrick - 29 May 2008 17:35 GMT

Boris,

Sub AddDynamicNamedRangeMacro()
Dim myName As String
Dim shtName As String

myName = "DynTable"
shtName = ActiveSheet.Name

On Error Resume Next
ActiveWorkbook.Names(myName).Delete
ActiveWorkbook.Names.Add Name:=myName, RefersToR1C1:= _
"=OFFSET('" & shtName & "'!R1C1,0,0,COUNTA('" & shtName & _
"'!C1),COUNTA('" & shtName & "'!R3))"
End Sub

HTH,
Bernie
MS Excel MVP

>I am trying to define a name in a sheet (the name of the sheet will change
> each, and I need function to insert into a macro, which will re-define this
[quoted text clipped - 10 lines]
>
> Any suggestions? Thx much.

Reply to this Message