Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to specify a range from arbitrary cell and to "end of worksheet"
Hello,
I would like some help with efficiently counting cells in a worksheet where neither the location of the last row or the last column 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 blank cell) 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 the last cell of a spreadsheet (or whatever Excel thinks is the last cell). Is there a way to reference THAT CELL that I'm taken to when I hit CTRL-END? Thanks, Alex |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to specify a range from arbitrary cell and to "end of worksheet" or: How to refer to worksheet's "last cell" without VBA?
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 the last row or the last column 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 blank cell) 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 the last cell of a spreadsheet (or whatever Excel thinks is the last cell). Is there a way to reference THAT CELL that I'm taken to when I hit CTRL-END? Thanks, Alex |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to specify a range from arbitrary cell and to "end of
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 - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to specify a range from arbitrary cell and to "end of worksheet" or: How to refer to worksheet's "last cell" without VBA?
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 - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to specify a range from arbitrary cell and to "end of worksheet" or: How to refer to worksheet's "last cell" without VBA?
You can find the last cell in the range using this array formula** :
=ADDRESS(MAX((B4:J15<"")*ROW(B4:J15)),MAX((B4:J15 <"")*COLUMN(B4:J15))) You'll notice that I'm testing a relatively small range. This formula returns the cell reference as a TEXT string. By itself, it can't be used as a reference in a formula. You'd need to do it like this array formula** : =COUNTA(INDIRECT("B4:"&ADDRESS(MAX((B4:J15<"")*RO W(B4:J15)),MAX((B4:J15<"")*COLUMN(B4:J15))))) The bigger the range you need to include the more inefficient this is. Another way without actually finding the reference for the last cell. Another array formula** : =COUNTA(OFFSET(B4,,,MAX(SUBTOTAL(3,OFFSET(B4:B15,, COLUMN(B4:J15)-COLUMN(B4),))),COUNTA(B4:J4))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) You can tell just by looking at these formulas that they are nowhere near as efficient as: =COUNTA(B4:J15) -- Biff Microsoft Excel MVP wrote in message ... 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
Excel 2007 PivotTable "arbitrary shape is not allowed when its elements cross a reference dimension" | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |