Automatically select last row in a set in Excel

2

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).

Canadian Luke

Posted 2012-12-01T04:25:37.527

Reputation: 22 162

Sorry about the confusion – cutrightjm – 2012-12-01T07:04:48.390

1I realized after I read the link and re-read my question. No harm done, thanks :) – Canadian Luke – 2012-12-01T07:05:24.603

Answers

1

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.

Daniel

Posted 2012-12-01T04:25:37.527

Reputation: 654

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 – 2012-12-02T07:01:56.030

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 – 2012-12-02T15:02:27.310

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 – 2012-12-02T18:38:36.930

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 – 2012-12-02T20:31:46.423

Asked: 2012-12-01T04:25:37.527

Viewed: 2 460 times

Active: 2012-12-02T05:41:06.330