Defining a Dynamic Range using a variable

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

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

"Alseikhan" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>
>
> --
> Alseikhan
> ------------------------------------------------------------------------
> Alseikhan's Profile:
> http://www.excelforum.com/member.php...o&userid=32364
> View this thread: http://www.excelforum.com/showthread...hreadid=526651
>

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


"Alseikhan" wrote:

>
> 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
>
>
> --
> Alseikhan
> ------------------------------------------------------------------------
> Alseikhan's Profile: http://www.excelforum.com/member.php...o&userid=32364
> View this thread: http://www.excelforum.com/showthread...hreadid=526651
>
>

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

"Greg Wilson" wrote:

> 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
>
>
> "Alseikhan" wrote:
>
> >
> > 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
> >
> >
> > --
> > Alseikhan
> > ------------------------------------------------------------------------
> > Alseikhan's Profile: http://www.excelforum.com/member.php...o&userid=32364
> > View this thread: http://www.excelforum.com/showthread...hreadid=526651
> >
> >