So there is NO way to refer to the CTRL-END cell in a formula?

There is but it's nowhere near as efficient as what I suggested. In fact, it
would be very inefficient. Still interested?

Finding the last used column is easy *but* finding the last used row is more
difficult since the number of rows varies by column and this is where you
lose all efficiency.


--
Biff
Microsoft Excel MVP


wrote in message
...
That's what I'm doing now... I think it's a little brittle to specify
an enclosed area like that and it doesn't look as "clean" to me as an
entire-row or entire-column reference. (i.e. A:A or 3:3)

So there is NO way to refer to the CTRL-END cell in a formula?

On Dec 10, 7:24 pm, "T. Valko" wrote:
Why not just use this:

=COUNTA(B4:IV10000)

That's about as efficient as you're going to get!

--
Biff
Microsoft Excel MVP

wrote in message

...



Hello,

I would like some help with efficiently counting cells in a worksheet
where neither the location of thelastrow or thelastcolumn are
known.

I have a worksheet where data is arranged in columns of various
lengths. Data begins in column B, and the first three rows of the
worksheet are not to be counted.

So, column B might have entries from B4 to B10 that I need to count,
while column C has entries from C4 to C25, and D has entries from D4
to D7 - very short.

Data is not all numerical.
I am guaranteed to have adjacent columns (i.e. first blank column
signals the end of data)
I am guaranteed to have adjacent data in each column (i.e. there is no
data below a blankcell)

My current formula is this:
=COUNTA(4:10000)-COUNTA(A4:A10000)

It counts the data in all rows starting with 4, and then subtracts
column A, which is not to be counted.

I would LIKE my formula to look like this:
=COUNTA(B4:last_cell)

It doesn't matter if last_cell is not exact. I know CTRL-END takes me
to thelastcellof a spreadsheet (or whatever Excel thinks is the
lastcell). Is there a way to reference THATCELLthat I'm taken to
when I hit CTRL-END?

Thanks,

Alex- Hide quoted text -

- Show quoted text -