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

[RolanDoobies]

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.

 

[Richie(UK)]

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]

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]

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

 

[stevebausch]

To perform a "seek" to the end/beginning of a region, it's End-Arrow.