How to specify a range from arbitrary cell and to "end of
Hi Biff,
In the interest of conserving computer cycles, I will use your
solution. However, I am curious about how I could refer to the last
cell without using VBA.
Thanks for your help!
-Alex
On Dec 11, 12:03 pm, "T. Valko" wrote:
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 -- Hide quoted text -
- Show quoted text -
|