Automatically select last row in a set in Excel

In Excel 2003, I am trying to keep track of some petty cash, and have it set up with the denomination along the top row, along with a sub total and difference column. I want a small section that shows how many rolls of coins I should have, by taking the total amount, and dividing it by however many should be in a roll, and rounding to the lowest whole number. That part is fine.

What I want done is for that ONE section (how many rolls) I should have, based on the last row that has information in it. For example, if the last row is row 13, it should read the data from `B13`, `C13`, `D13`, etc.

I don't mind learning Macros, if that's what the solution requires. I don't want to be manually selecting the last row each time though, I just want the worksheet to know automatically.

EDIT: What I mean is that I want to have the last cells be selected for a formula, `INT(B2/25)`, replacing `B2` with the last row, and the number from 2-6 (depending on the cell).

-
Sorry about the confusion – cutrightjm Dec 1 '12 at 7:04
I realized after I read the link and re-read my question. No harm done, thanks :) – Canadian Luke Dec 1 '12 at 7:05

Assuming you have no skipped rows in column B, this will return the value of the last used cell in Column B. `=INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A))))`

So your completed formula would be something like:

`=INT(INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A))))/25)`

This is using `ROWS(A:A)` instead of hardcoding the number of rows so that it will still work when you upgrade from Excel 2003.

It uses `Row()+1` so that you can freely place this in Column B itself without having the issue of circular references.

Because it uses `Row()+1` will not work correctly if placed in a row below the row of the last value in column B.

-
This just returns a 0. I tried reading the help file about `INDIRECT`, but it isn't making sense to me. Can you explain it a bit better? – Canadian Luke Dec 2 '12 at 7:01
Indirect allows you to reference a cell by providing a the text of the reference. So something like `Indirect("A" & B1)` would end up being Column A and then whatever Row # corresponds to the value of B1. The only reason what I provided is resulting in 0 is that you must have at least 1 skipped row in Column B, as in a blank cell. If you want to keep the blank cells add a minus # of blank cells just before the last closing parenthesis, like so: `=INDIRECT("B" &ROWS(A:A)-COUNTBLANK(INDIRECT("B" & ROW()+1 &":B" & ROWS(A:A)))-1)` – Daniel Cook Dec 2 '12 at 15:02
I got it working with that last command you gave me, but I took out the `-1` at the end. Thank you very much for your help – Canadian Luke Dec 2 '12 at 18:38
I figured out why it kept giving me a 0. It only works if the last row is the row the formula is on, or lower – Canadian Luke Dec 2 '12 at 20:31