Dynamic range doesn't work with blank rows.

  • Thread starter Thread starter Jay Fincannon
  • Start date Start date
J

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?
 
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.
 
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.
 
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
 
Back
Top