Summation where range vary using Macro

halogen_bulb · #1 11-15-2011, 01:27 AM

Hi,

How do I improve this code by getting the summation of the same column with varying rows?

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

Im pretty sure there is a way to do it but I dont know how.

Please help

Thanks,

Pecoflyer · #2 11-15-2011, 03:53 AM

Cross post

http://www.excelforum.com/excel-prog...ml#post2642650

halogen_bulb · #3 11-15-2011, 05:38 AM

Solution I gathered form this forum 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

[SOLVED]

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Print Macro w/ Page Range?	stickyit	Outlook	1	10-06-2011 09:51 AM
Work Week: hours vary each day	rkc	Outlook	1	08-31-2011 01:17 PM
Generic range for custom sort macro?	tswiers	Excel Programming	2	08-11-2011 02:40 AM
use VBA to name a range	g48dd	Excel Programming	7	06-20-2011 06:34 AM
Sort Range	boutells	Excel	1	07-15-2009 03:02 AM