Dynamic Named Rages

  • Thread starter Thread starter Dave Watkins
  • Start date Start date
D

Dave Watkins

Hi

I'm trying to get multiple dynamic named ranges to work on the same
sheet with multiple chuncks of data decending down the page. I'd like
each range to stop when it hits an empty cell as they are my seperaters.

I've found out how to do normal dynamic named ranges with OFFSET but
that doesn't work if you have any data underneath it as the COUNTA
includes that in the range size. I'd like the range to stop when it hits
an empty cell/row so I can start me next range on the next row.

Is this even possible?

Thanks
Dave
 
Since your named ranges are separated by one blank row, you'll probably
add new items by inserting a cell, or a row, within the list range.

In that case, you could give each range a static name, and it will
expand as new items are added within the range.
 
Not quite. My rages are generated by SQL queries, so rows are not
inserted as such. Unless I'm missing something.
 
Hi Dave,

One way would be to build a volatile function called something like
COUNTCONTIGROWS which used range.end(xldown) to count the number of
contiguous visible rows, and then use this function in your dynamic range.
(thats the way it is done in the FastExcel V2 dynamic range wizard).

If you are using VBA to pur your query into a recordset, and then
COPYFROMRECORDSET to put the recordset onto the excel sheet another way
would be to generate the defined name with the correct address at the same
time.

regards
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
Hi Richard

I've solved this in what I considered a strange but easy way. Basically
you can use COUNT(<Name of Query>) and it will return the number of rows
returned from the Query. You then add this to the standard OFFSET
function used for creating dynamic named ranges and it all works out
perfectly.

Dave
 
Back
Top