Dynamic range doesn't work with blank rows.

[Jay Fincannon]

I have a sheet with 120 rows 18 of which are blank, not all together
but a single blank row between sets of entries:
myRange refers to; =OFFSET(North!$C$1,0,0,COUNTA(North!$C:$C),1)
Column C contains street names
A1 =COUNTA(myRange) The result is 88 not 102 as expected. What
happened to the other 14 non empties?

 

[Dale Hymel]

You named the range myRange =offset(North!$C$1,0,0,counta(North!$C:$C),1)
This will return a range C1:C102 (120 minus the 18 Blank Rows)

The when you use CountA(myRange) it returns 88 because some of the rows
within C1:C102 are still blank.

 

[Aladin Akyurek]

Try...

=North!$C$1:INDEX(North!$C:$C,MATCH(REPT("z",255),North!$C:$C))

instead.

 

[Jay Fincannon]

That done the trick Aladin thanks
Jay

Try...

=North!$C$1:INDEX(North!$C:$C,MATCH(REPT("z",255),North!$C:$C))

instead.

 

[Jay Fincannon]

By the way Aladin, how does this work?
I entered =MATCH("zzzzz",$C:$C) in a cell on the North sheet.
According to Help I should have gotten an error.

 

[Aladin Akyurek]

You should use highly improbable REPT("z",255) instead of just "zzzz". The
former is a "string constant" that is latest in ascending lexical ordering.
The way MATCH gets the job done with REPT("z",255) as lookup value is
similar to when 9.99999999999999E+307 is the lookup value. See:

http://makeashorterlink.com/?Z20312BD6