how is a macro made that totals, but needs to work with diff

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
I made a macro that will total columns , but when i try to use it on a sheet that has a different ending row, i have to scroll to row 2156 to find the total.. How do i make it total with different ending rows? I want to use it for several different spreadsheets.All the colums are the same, its just the rows that are different.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi RolanDoobies,

It sounds as if you need to use something like:

Range("A65536").End(xlup).Select

This starts from the bottom of your chosen column, A in this case, and works its way up until it finds a cell with data in it. Just use Offset to move one or two rows down and then put in your total figure.

HTH
 

RolanDoobies

Board Regular
Joined
Aug 28, 2002
Messages
99
Thanks for the answer, but i dont have a clue as to how to write code. I just use the macro record button. Do i jus insert that li ne into the macro somewhere? This is what it looks like now.
Range("C2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("D2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("E2795").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("G2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("H2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("J2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("K2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("L2795").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("M2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("N2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("O2795").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
Range("P2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
Range("Q2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
ActiveWindow.SmallScroll ToRight:=7
Range("X2795").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2793]C:R[-1]C)"
End Sub
 

Ekim

Well-known Member
Joined
Jun 30, 2002
Messages
1,416
I'm not sure whether the following macro will help (macro courtesy of http://geocities.com/aaronblood)

Just select any cell with data in your range and run the macro (or set the macro to a button). You'll see the SUM formulas added at the bottom of each column (even if some rows have different row endings.


Sub QuickTotals()
r = ActiveCell.CurrentRegion.Rows.Count
Set SumRow = ActiveCell.CurrentRegion.Offset(r, 0).Resize(1)
SumRow.FormulaR1C1 = "=SUM(R[" & -r & "]C:R[-1]C)"
SumRow.Offset(0, -1).Resize(1, 1).Value = "Totals"
End Sub


Regards,

Mike
 

Forum statistics

Threads
1,144,437
Messages
5,724,356
Members
422,546
Latest member
KevinOnTheDaily

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top