Question about CountA() and dynamic named ranges

[Eric58132]

Hi All,

I have a file that is completely overloaded with "stuff", and it takes an exceptionally long time to recalculate. Because of this, I'm trying to optimize the speed of the file.

There are over a thousand calculations done involving sumproducts/sumifs/other arrays that use a range moving from A51:A65000 in my file (for example), and I am trying to optimize these calculations by implementing dynamic named ranges.

The site I found to help me in this is here:
http://www.ozgrid.com/Excel/DynamicRanges.htm

Within this page lies the formula (slightly modified) that I used for my ranges. It is =OFFSET($A$1,0,0,COUNTA($A:$A),1)
In this example with A51:A65000, I modified the formula to read
=OFFSET($A$51,0,0,COUNTA($A:$A),1)
....Now here is my problem. Is there a way to change the CountA() portion of the formula to not read the entire "A" column? Basically I'm looking for something like CountA($A$51:$A), but if I want to name the starting point of my range, Excel forces me to name the end as well, which contradicts the whole point of this exercise in the first place.

Can anyone help?

[Ken Puls]

Curious, does CountA($A$51:$A$65536) work? Just an FYI though... bring that to Excel 2007+ and you're now no longer going to the end of the sheet, as the new file formats have over 1 million rows.

[Eric58132]

it does work Ken, but doesn't that defeat the purpose of my trying to define dynamic named ranges in the first place? I was doing it to avoid doing hLookups/sumproduct arrays with 65000 rows when it was unnecessary.

[Ken Puls]

Someone will come along and give me hell for this, but to be honest what I'd do is create the named range on my valid data, then use VBA to update it when it changes... Maybe refresh all the named ranges at open, just to be safe.

It kind of depends how much the sheet changes, and if data will be modified on it frequently.

[Eric58132]

so there really is no way to define the starting range of countA without additionally defining an end?

[Ken Puls]

Well no... that's what the CountA portion on the entire column does. It returns the number of rows of data.

I'm not an expert when it comes to using that formula though, so I can't really say how much more efficient it would be to use that over vlookups and sumproducts over the entire range.

I would think it would be more efficient though, as the dynamic range is only going to be recalculated if one of it's inputs changes. By contrast, your vlookup and sumproduct formulas need to look at the greater range every time they are calculated.

Hopefully someone will chip in that has a deeper understanding of how that formula works.

[Bob Phillips]

Why not just use

=OFFSET($A$51,0,0,COUNTA($A:$A)-COUNTA($A$1:$A$50),1)

[Eric58132]

well that sounds pretty interesting and way too easy. I'll give that one a shot. Will report back.