Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- Alseikhan ------------------------------------------------------------------------ Alseikhan's Profile: http://www.excelforum.com/member.php...o&userid=32364 View this thread: http://www.excelforum.com/showthread...hreadid=526651 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Dynamic Range using a variable
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Dynamic Range using a variable
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a Dynamic Range using a variable
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help defining dynamic range | Charts and Charting in Excel | |||
Defining Dynamic Range | Excel Programming | |||
Defining a variable Range for cells with values in them! | Excel Programming | |||
Defining a Variable Range | Excel Programming | |||
Refreshing a dynamic range variable | Excel Programming |