Sum columns based on number of entries in column A - code efficiency

[fddekker]

I know this question of auto summing columns comes up often, and that there are several ways of doing it. My specific question is, how can I make my code more efficient. It just feels like there must be a better way:
I followed the following approached:
(1) determined where the last cell is in columns A
(2) relative to this cell, populate with a formula.

Is there a way to have entered the formula in all the cells at once?
Below is an extract from a data set, as well as a portion of the code that contains the above mentioned question.

Data.xlsx
	H
9
Other

Sub RepForm()
Dim LR As Long
' Enter Total formula, copy down, and enter column totals '
Range("F1").Select
ActiveCell.FormulaR1C1 = "Total"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, "A").End(xlUp).Row)
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Cells.Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 1).Value = "=SUM(B2:B" & LR - 1 & ")"
ActiveCell.Offset(1, 2).Value = "=SUM(c2:c" & LR - 1 & ")"
ActiveCell.Offset(1, 3).Value = "=SUM(d2:d" & LR - 1 & ")"
ActiveCell.Offset(1, 4).Value = "=SUM(e2:e" & LR - 1 & ")"
ActiveCell.Offset(1, 5).Value = "=SUM(f2:f" & LR - 1 & ")"
Columns("B:F").EntireColumn.AutoFit
' Freeze top row and group the rows '
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.Group
Range("A1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

 

[Michael M]

Maybe ths way

Sub RepForm()
Dim LR As Long
' Enter Total formula, copy down, and enter column totals '
applicaton.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("F1").Value = "Total"
Range("F2:F" & LR).Formula = "=SUM(B2:E2)"
Range("B" & LR + 1 & ":F" & LR + 1).Formula = "=SUM(B2:B" & LR - 1 & ")"
Columns("B:F").EntireColumn.AutoFit
' Freeze top row and group the rows '
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.Group
Range("A1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
applicaton.ScreenUpdating = True
End Sub

 

[fddekker]

Maybe ths way

Sub RepForm()
Dim LR As Long
' Enter Total formula, copy down, and enter column totals '
applicaton.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("F1").Value = "Total"
Range("F2:F" & LR).Formula = "=SUM(B2:E2)"
Range("B" & LR + 1 & ":F" & LR + 1).Formula = "=SUM(B2:B" & LR - 1 & ")"
Columns("B:F").EntireColumn.AutoFit
' Freeze top row and group the rows '
ActiveWindow.SplitRow = 1
ActiveWindow.FreezePanes = True
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.Group
Range("A1").Select
ActiveSheet.Outline.ShowLevels RowLevels:=1
applicaton.ScreenUpdating = True
End Sub

 

[fddekker]

Thanks!!

 

[Michael M]

glad to help..

 

[Peter_SSs]

Below is an extract from a data set, as well as a portion of the code that contains the above mentioned question.

Data.xlsx
	H
9
Other

I am mainly posting to point out that when using XL2BB you need to first select the range that you want to show in the forum before clicking 'Mini Sheet'

However, I also wondered about what is actually in row 'LR'?
Your code that enters the formulas in column F includes inserting a formula in row 'LR' that sums the values in that row.
However, the SUM formula being entered in row LR + 1 is a sum excluding any numbers in row LR. Is that actually what you want?

Further, selecting ranges in vba is rarely needed and slows your code. Michael has removed a couple of the 'selections' but here is another one that could be employed in your code. It also shortens the code considerably.

Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Rows.Group

Rows("2:" & LR).Group