PDA

View Full Version : Solved: Summarizing a Range



dakota
08-31-2006, 12:25 PM
I'm looking for help on a macro I'm creating for one of my files. The purpose is to download a large number of data records, sort them by common types of equipment, then copy and paste each set of sorted transactions into separate worksheets. I've been able to get that far, but the next step I want the macro to perform is to summarize all the costs (which is located in column H) and return a total 2 cells below the last data line regardless of the number of transactions.

The problem I am having is that the macro in summarizing a concrete range (H4:H14) and will never change. As the number of transactions continue to increase, this range will leave out anything after row 14. I really just want something that sums a range from the first record to the last record of the column.

Any suggestions would be greatly appreciated.

mvidas
08-31-2006, 12:42 PM
Hi Dakota,

Welcome to VBAX! Usually it helps if you paste the code you have so far, but in this case we can help without seeing it (though it may need to be modified a little to have it pertain to a specific worksheet).

What you can do is have the macro start at the bottom of the sheet in that column and press control-up to get to the last used cell in the column, then go down 2 cells. The "End" method can do the control-up portion, and the Offset method can go down 2 cells. Then the FormulaR1C1 can have it sum from row 4 in the column to the last used cell:Cells(Rows.Count, "H").End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R4C:R[-2]C)"Please let us know if you have any questions!
Matt

dakota
08-31-2006, 01:43 PM
That worked great!!! Thanks for the quick response.

mvidas
09-01-2006, 06:37 AM
Glad to help! Please let us know if you have any other questions!

Also, as you're new to the forum here, when a question you've asked has been answered to your liking, you can go to "Thread Tools" at the top of the page, and select the option "Mark Thread Solved". This changes the subject line of the thread so it begins with "Solved: ", it lets everyone know you're all set. I've done it for you on this one, but just for future reference :)

Matt