Defining a named range for a dynamic result set

[Guest]

In a workbook template that will be run for each month’s 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]

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.

 

[Guest]

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
here:

http://www.contextures.com/xlNames01.html#Dynamic

HTH
Regards,
Garry