sum last row in a range

[koi]

Hi All,

here I am again with different question, hopefully I will get the result as always from this lovely forum, Thanks

below code will do fine for sum last row from B, but how to copy it to C and D on the last row as well?

Sub SumTotal()
Dim Lr As Long
Sheets("Test").Select
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & Lr + 1).Formula = "=SUM(B2:B" & Lr & ")"
End Sub[\Code]

 

[Hui]

If all the columns have the same height

Sub SumTotal()
Dim Lr As Long
Sheets("Test").Select
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & Lr + 1).Formula = "=SUM(B2:B" & Lr & ")"
Range("C" & Lr + 1).Formula = "=SUM(C2:C" & Lr & ")"
Range("D" & Lr + 1).Formula = "=SUM(D2:D" & Lr & ")"

End Sub

If they are different heights
If all the columns have the same height

Sub SumTotal()
Dim Lr As Long
Sheets("Test").Select
Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & Lr + 1).Formula = "=SUM(B2:B" & Lr & ")"
Lr = Range("C" & Rows.Count).End(xlUp).Row
Range("C" & Lr + 1).Formula = "=SUM(C2:C" & Lr & ")"
Lr = Range("D" & Rows.Count).End(xlUp).Row
Range("D" & Lr + 1).Formula = "=SUM(D2:D" & Lr & ")"

End Sub

 

[koi]

Hi Hui,

so i have to repeat the range and formula, the problem is i have till AF

but i think if there is no other way then i will keep repeat those, Thanks again

 

[Marc L]

Hi !

For same height, you can enter formula at once for all range
like you directly do in Excel : select range - first cell activated - and
enter formula like for B column … That's all !
And if you activate Macro recorder, you have the code …
​

 

[koi]

Hi Marc,

when i activate macro rec and select from B to AF, it will give something like Range("B75:AF75").select

but how if the rows add up to 85?

 

[Marc L]

You just have to select the total range to enter column B formula …​

 

[koi]

Hi Marc,

can you give some more pointer? or example since all i've done with the recorder cannot give me dynamic rows if i adding more rows

 

[Nebu]

Hi:
why don't you convert your data range in to a table and use the in-build sum function in table, you do not need a macro for this and it is dynamic.

Thanks

 

[Hui]

  Dim Lr As Long
  'Sheets("Test").Select
  Lr = Range("A" & Rows.Count).End(xlUp).Row
  Range(Cells(Lr + 1, 2), Cells(Lr + 1, 32)).FormulaR1C1 = "=SUM(R[-" + CStr(Lr - 1) + "]C:R[-1]C)"

 

[NARAYANK991]

Hi ,

Another couple of ways.

Sub SumTotal1()
    Dim Lr As Long
   
    ThisWorkbook.Worksheets("Test").Select

    For col = ActiveSheet.Columns("A").Column To ActiveSheet.Columns("AF").Column
        Lr = Cells(Rows.Count, col).End(xlUp).Row
        Cells(Lr + 1, col).Formula = "=SUM(" & Cells(2, col).Address & ":" & Cells(Lr, col).Address & ")"
    Next
End Sub

Sub SumTotal2()
    Dim Lr As Long, maxLr As Long
   
    ThisWorkbook.Worksheets("Test").Select
   
    maxLr = ActiveSheet.Range("A2").CurrentRegion.Rows.Count + 1
   
    Range("A" & maxLr + 1 & ":" & "AF" & maxLr + 1).Formula = "=SUM(A2:A" & maxLr & ")"
End Sub

Narayan

 

[koi]

Thanks All for the helping,

Hui solution work best although Narayank second one also works,

regards, Koi

 

[Marc L]

or example since all i've done with the recorder cannot give me dynamic rows if i adding more rows

L& = ActiveSheet.UsedRange.Rows.Count
Range("B" & L + 1 & ":AF" & L + 1).Formula = "=SUM(B2:B" & L & ")"