Naming dynamic ranges with VBA and INDIRECT

for the risk for hijacking another users thread i thought i'd start a new one on a similar topic.
http://excelforum.com/showthread.php?t=631719

1. i'm curious to know whether a named dynamic range in VBA can overcome the inability of INDIRECT to resolve a dynamic range? that is, define a dynmaic range using VBA, display that range name in say cell A1, then INDIRECT(A1) to utilise the dynamic range?

2. if so, i was thinking of using the standard OFFSET to determine rows and columns, yet now realise that this will not handle data with blank rows/columns. if i have a dataset that is for example cells A1:A10, A:12:A22, A24:A30 what formula should i use to ensure the blank cells are ignored?

Hello Inky,

It isn't very clear what you want to do. Did you want to create a dynamic range in VBA then insert the name into a cell, say A1, and use INDIRECT to return what exactly?

Why do you want to do this with VBA and worksheet formulas? The VBA code can also resolve blanks, and non contiguous ranges much more easily. Using the worksheet formulas seems needlessly complicated. You explain more about what you want the code to do.

Sincerely,
Leith Ross

hi leigh

sorry for the ambiguity. i had a brain freeze.

i want to create dynamic ranges in VBA then insert that name into a cell (let's say B11, B12 and B13).

i have 6 worksheets all identical in calculation yet referring to 6 different reports (one worksheet for each report, teh report being the data range). the worksheets contain a SUMIF/INDEX/MATCH calculation to extract appropriate elements from the range. the INDIRECT is used to refer to the appropriate range. here's the formula, which by itself probably doesn't mean much:

=SUMIF(INDIRECT($B$12),$F35,INDEX(INDIRECT($B$11),0,MATCH(J$32,INDIRECT($B$13),0)))

i'm using formulas as the worksheets are a template in an application. when a new report (data range) is created an additional worksheet will be inserted into the model, dynamic ranges determined and appear on the worksheet and caluclations performed. also, the template needs to be easily updated by novice Excel users who want to see the formulas and be able to "evaluate" them.