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]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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