Defining a Dynamic Range using a variable

[Alseikhan]

Can one define a range Name (using Insert > Name > Define) where height
of range (i.e., # of rows) is defined by a variable whose value changes
in VBA code?
For example, this range my_rng is defined as:
=offset(Sheet1!A1,0,0,xrows,1)

where xrows is a public variable whose value changes frequently.

Of course, the way the name is defined above mandates that xrows is
another range already defined in workbook, which is something I am
trying to avoid.
Thank you.

Alseikhan

 

[Charles Williams]

You would need to define xrows as a volatile UDF, say COUNTROWS() which
returned the value of your VBA variable.


Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Greg Wilson]

Have named range RefersTo property set as follows. Note parentheses in
"xrows()" which is a public function:

=Offset(Sheet1!$A$1, 0, 0, xrows(), 1)

Then in a standard module:

Option Explicit
Dim R As Integer

Sub ModifyNamedRange()
'Other VBA code goes here
R = 41 'Have VBA set value of R variable
End Sub

Public Function xrows() As Integer
Application.Volatile
xrows = R
End Function

Regards,
Greg

 

[Greg Wilson]

You may want to make R a public variable instead and set it to a default
value greater than zero on workbook open else R's default value of zero makes
the Offset formula return an error. Also, experiment with not using
Application.Volatile. You may not need it. Volatile functions are run every
time calculation occurs.

I see Charles beat me.

Regards,
Greg