offset function in defining name

[BorisS]

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.

 

[Bernie Deitrick]

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