Naming Ranges

**j_Southern** · 02-09-2012, 07:32 AM

Hi,
I like to define a dynamic range in the define name window as follows

 =Offset($A$1,0,0,Counta($A:$A),Counta($1:$1)

This works fine as long as all the cells in col A and row 1 are filled.
If this is not the case I have defined the range in VBA. using rows.count and columns.count

I now have a situation where I would like to define a dynamic range which may easily have lots of empty cerlls using the define name window. I have not been able to find worksheet functions to do the job.
Can anyone help please
John

**Bob Phillips** · 02-09-2012, 07:46 AM

Try this

=OFFSET($A$1,0,0,MATCH(REPT("z",99),$A:$A),MATCH(REPT("z",99),$1:$1))

**j_Southern** · 02-09-2012, 09:20 AM

Hi,
Thanks for your help, but that doesn't work.
John

**NBVC** · 02-09-2012, 09:24 AM

What kind of data do you have in Column A and Row 1?

**j_Southern** · 02-09-2012, 10:30 AM

A mixture of strings and numbers and blanks
John

**NBVC** · 02-09-2012, 01:47 PM

Try:

=Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,MAX(IFERROR(MATCH(9.999E+307,Sheet1!$A:$A),0),IFERROR(MATCH(REPT("z",255),Sheet1!$A:$A),0)),MAX(IFERROR(MATCH(9.999E+307,Sheet1!$1:$1),0),IFERROR(MATCH(REPT("z",255),Sheet1!$1:$1),0)))

This takes into account that it might be possible to have only text (and blanks) or only numbers (and blanks) too...

**Bob Phillips** · 02-10-2012, 07:27 AM

Originally Posted by j_Southern

A mixture of strings and numbers and blanks
John

=OFFSET($A$1,0,0,MAX(IFERROR(MATCH(99^99,$A:$A),0),IFERROR(MATCH(REPT("z",99),$A:$A),0)),MAX(IFERROR (MATCH(99^99,$1:$1),0),IFERROR(MATCH(REPT("z",99),$1:$1),0)))

**j_Southern** · 02-13-2012, 09:57 AM

Thank you all for your help. Both methods work. I shall use the shorter one.
It does seem odd that there isn't a function like VBA CurrentRegion which would make it easier to name a range containing blank cells..
John