VBA to SUM Variable range

marcdenney1

New Member
Joined
Sep 24, 2014
Messages
42
Hi All,

I have a spreadsheet where the data starts in D3 but the end column is variable

I'm looking for some VBA to SUM everything in the row after the last column.

So far I have:

Code:
Range("D2").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Total"
    ActiveCell.Offset(1, 0).Select
    
    Dim LastColumn As Long
    LastColumn = Range("XFD3").End(xlToLeft).Column + 1
    Cells(LastColumn, 1).Formula = "=Sum(D3:3" & LastColumn - 1 & ")"

Its erroring on the SUM

Can anybody help?

Thanks

Marc
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Lots of ways to do this, one suggestion below:
Code:
Sum DynamicLastCol()

Dim rng         As Excel.Range
Dim sFormula    As String

Set rng = Cells(2, Columns.Count).End(xlToLeft)
sFormula = "=SUM(D2:@1)"

With rng.Offset(, 1)
    .Value = "Total"
    .Offset(1).Formula = Replace(sFormula, "@1", rng.Offset(1).Address)
End With

Set rng = Nothing

End Sub
 
Upvote 0
That works perfectly, thank you.

Is there an easy way to do it for multiple rows? (it will always be the same row numbers)

Thanks for your help

Marc

Lots of ways to do this, one suggestion below:
Code:
Sum DynamicLastCol()

Dim rng         As Excel.Range
Dim sFormula    As String

Set rng = Cells(2, Columns.Count).End(xlToLeft)
sFormula = "=SUM(D2:@1)"

With rng.Offset(, 1)
    .Value = "Total"
    .Offset(1).Formula = Replace(sFormula, "@1", rng.Offset(1).Address)
End With

Set rng = Nothing

End Sub
 
Upvote 0
Multiple rows meaning? At a guess for what you mean, try:
Code:
Sub DynamicLastCol()

Dim rng         As Excel.Range
Dim sFormula    As String
Dim LR          As Long

Set rng = Cells(2, Columns.Count).End(xlToLeft)
LR = Cells(Rows.Count, 2).End(xlUp).Row

sFormula = "=SUM(D2:@1)"

With rng.Offset(, 1)
    .Value = "Total"
    Set rng = .Offset(1)
End With

rng.Resize(LR - rng.Row + 1).Formula = Replace(sFormula, "@1", rng.Offset(1).Address(False, False))

Set rng = Nothing

End Sub
 
Upvote 0
I would like it to work for rows 5,7,9,11,13,15 also

thanks

Multiple rows meaning? At a guess for what you mean, try:
Code:
Sub DynamicLastCol()

Dim rng         As Excel.Range
Dim sFormula    As String
Dim LR          As Long

Set rng = Cells(2, Columns.Count).End(xlToLeft)
LR = Cells(Rows.Count, 2).End(xlUp).Row

sFormula = "=SUM(D2:@1)"

With rng.Offset(, 1)
    .Value = "Total"
    Set rng = .Offset(1)
End With

rng.Resize(LR - rng.Row + 1).Formula = Replace(sFormula, "@1", rng.Offset(1).Address(False, False))

Set rng = Nothing

End Sub
 
Upvote 0
Try:
Code:
Sum DynamicLastCol()

Dim rng         As Excel.Range
Dim x           As Long
Dim sFormula    As String

Set rng = Cells(2, Columns.Count).End(xlToLeft)
sFormula = "=SUM(D2:@1)"

Application.ScreenUpdating = False

With rng.Offset(, 1)
    .Value = "Total"
    .Offset(1).Formula = Replace(sFormula, "@1", rng.Offset(1).Address(False, False))
End With
Set rng = rng.Offset(1)

For x = 5 To 15 Step 2
    rng.Copy
    Range("D" & x).PasteSpecial
Next x

ActiveSheet.Calculate

Set rng = Nothing

Application.ScreenUpdating = True

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top