Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a named range for a dynamic result set
In a workbook template that will be run for each months production numbers,
several hidden worksheets are updated with data from MS query from an Access db. The first column of each contains a value used to discriminate between new or renewal business, while the second column is used by worksheets the user can see to automatically display data using VLOOKUP functions. I have manually defined the range of data for new and renewal, but would like to create VBA code that will do it automatically when the template is opened each month. I am having trouble redefining the range once I know its dimensions. When I record the range define steps in a macro, the following code is generated: Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select ActiveWorkbook.Names.Add Name:="rngPrgmYTD", RefersToR1C1:= _ "='LU-PRGMYTD'!R1C1:R14C8" How would I replace the 14 and 8 in the R14C8 reference with variables? Also, assuming the name already exists, what would the statement syntax be to resize the already-defined named range? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a named range for a dynamic result set
I'd use something like:
dim myRng as range Dim LastRow as long Dim LastCol as long with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row lastCol = .cells(1,.columns.count).end(xltoleft).column set myrng = .range("A1",.cells(lastrow,lastcol)) end with myRng.name = "rngPrgmYTD" This actually uses the last used column in Row 1 and the last used row in column A. Keith B. wrote: In a workbook template that will be run for each months production numbers, several hidden worksheets are updated with data from MS query from an Access db. The first column of each contains a value used to discriminate between new or renewal business, while the second column is used by worksheets the user can see to automatically display data using VLOOKUP functions. I have manually defined the range of data for new and renewal, but would like to create VBA code that will do it automatically when the template is opened each month. I am having trouble redefining the range once I know its dimensions. When I record the range define steps in a macro, the following code is generated: Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select ActiveWorkbook.Names.Add Name:="rngPrgmYTD", RefersToR1C1:= _ "='LU-PRGMYTD'!R1C1:R14C8" How would I replace the 14 and 8 in the R14C8 reference with variables? Also, assuming the name already exists, what would the statement syntax be to resize the already-defined named range? Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a named range for a dynamic result set
Hi Keith,
You might want to define the range as a dynamic range so when your code changes its size, Excel adjusts for that automatically. You can get more info he http://www.contextures.com/xlNames01.html#Dynamic HTH Regards, Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking up a returned value defining a named range | Excel Discussion (Misc queries) | |||
Defining a custom-named Range across several worksheets | Excel Worksheet Functions | |||
help defining dynamic range | Charts and Charting in Excel | |||
Defining a Dynamic Range using a variable | Excel Programming | |||
Defining Dynamic Range | Excel Programming |