Summation where range vary using Macro

halogen_bulb · Nov 15, 2011

Hi,

I was able to create a macro to sum up a range of every page but that was fixed only on specific numberof rows. The problem arises when size of the rows on every page vary and the result of the sum will not be the actual value of every page rather the sum up is overlapped or shortened. Sumif is better but during submission to our client, every page should come with a simple summation.

Here is the code I use.

Code:

Sub sum()
'
' sum Macro
'    
    ActiveCell.FormulaR1C1 = "=SUM(R[-48]C:R[-1]C)"
    
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

I am attaching the sample of my worksheet. Im pretty sure there is a way to do it but I dont know how.

Please help.

Thanks,

halogen_bulb · Nov 15, 2011

Solution I gathered form this forums and other forums

Code:

Sub sumx()
Dim LastRow As Long
LastRow = Range("P65536").End(xlUp).Row + 1
     ActiveCell.Select
    Selection.Formula = "=SUM(P" & LastRow + 1 & ":" & Format(ActiveCell.Row - 1, "P#") & ")"

End Sub

Summation where range vary using Macro

halogen_bulb

New member

halogen_bulb

New member

Similar threads

Users who are viewing this thread