Named Ranges anomaly(?)
 

I've got several named ranges. And I use those named ranges in some
conditional formatting. Initially, I had the whole columns selected
like below, but that was taking a lot of memory, and I've been having
issues with inserting/deleting lines taking a VERY long time to
complete. But at least the conditional formatting worked.

=QueryBuster!$B:$AO

To try and help with the memory issue, I tried a dynamic range like
below. Those were accepted by the system, but my conditional
formatting broke. It's like it's not seeing the range.
=OFFSET(QueryBuster!$B$1:$AO$1,0,0,COUNTA(QueryBus ter!$B:$B))

So I went to a static range that would at least encompass the largest
dataset that I could imagine, like below.
=QueryBuster!$B$1:$AO$5000

But after I saved and next updated the workbook, the conditional
formatting still didn't work, and when I went and looked at the named
ranges, they had changed to similar as below. All of the first
dimension were at 1048537.
=QueryBuster!$B1048537:$AO4960

So, three questions:
1) What might be the possible causes for the memory issue, and
how might I resolve that?
2) What is up with the Named Ranges?
3) What's the best way to go about this?

Thanks.

Named Ranges anomaly(?)
 

Firstly, your named ranges are column-absolute, row-relative. This can
result anomolies depending which cell was active when the name was
created.

My practice is to give the the header row a local scope, fully absolute
defined name...

Name: Sheet1!My_Hdr
RefersTo: =Sheet1!$B$1

...so my dynamic range has a 'base point'. Now I can make a dynamic
range as follows...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(AO1))

...so the data range begins in row2 because I subtract the header in the
sizing. You could also use a defined name ref for the rightmost column
as follows...

Name: Sheet1!LastCol
RefersTo: =Sheet1!$AO$1

...so your defined name is...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(LastCol))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Named Ranges anomaly(?)
 

Ah, I see what you are talking about and what I did wrong. I'll fix
that. Thank you!!

Frank

On Sat, 16 Apr 2016 01:50:43 -0400, GS wrote:

Firstly, your named ranges are column-absolute, row-relative. This can
result anomolies depending which cell was active when the name was
created.

My practice is to give the the header row a local scope, fully absolute
defined name...

Name: Sheet1!My_Hdr
RefersTo: =Sheet1!$B$1

..so my dynamic range has a 'base point'. Now I can make a dynamic
range as follows...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(AO1))

..so the data range begins in row2 because I subtract the header in the
sizing. You could also use a defined name ref for the rightmost column
as follows...

Name: Sheet1!LastCol
RefersTo: =Sheet1!$AO$1

..so your defined name is...

Name: Sheet1!MyData
RefersTo: =OFFSET(My_Hdr,1,0,COUNTA($B:$B)-1,COLUMN(LastCol))