Excel VBA to Sum to bottom of column of data

[jehuh]

Hello All,

I'm working on creating a sheet that can start at the top of a column of data, Cell E19, and Place a SUM of the costs in Column E at the bottom, one cell down from the last piece of data. The number of rows can change, but the header will always start in E19. So far I have written the code to start at cell E19, go to the last cell, and place "Total" and "Expected WD Credit" under the dataset.

I want the SUM to go to the right of "Total"

Sub Sum_Stocklift_Column()

Range("E19").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2).Select
ActiveCell.FormulaR1C1 = "Total:"
ActiveCell.Offset(2).Select
ActiveCell.FormulaR1C1 = "Expected WD Credit:" 'code down to this line goes to bottom of cost column and types totals'
ActiveCell.Offset(-2, 1).Select 'move from active cell ("Expected WD Credit"), up 2 cells and right 1 cell'
ActiveCell.Offset(-1, 0).Select 'move up from cell to the right of "Total"'

End Sub

 

[Joe4]

Try this:

Sub Sum_Stocklift_Column()

    Dim lr As Long
    
'   Find last row in column E with data
    lr = Cells(Rows.Count, "E").End(xlUp).Row
    
'   Place word "Total" in column E two rows down form last data row
    Cells(lr + 2, "E").Value = "Total:"

'   Place total at bottom of column F
    Cells(lr + 2, "F").Formula = "=SUM(E19:E" & lr & ")"
    
End Sub

 

[jehuh]

Try this:

Sub Sum_Stocklift_Column()

    Dim lr As Long
   
'   Find last row in column E with data
    lr = Cells(Rows.Count, "E").End(xlUp).Row
   
'   Place word "Total" in column E two rows down form last data row
    Cells(lr + 2, "E").Value = "Total:"

'   Place total at bottom of column F
    Cells(lr + 2, "F").Formula = "=SUM(E19:E" & lr & ")"
   
End Sub

Made some edits to this (my mistake explaining what I was trying to accomplish) and it works perfectly!! Below is your code with my edits. Thank You for making it so easy to follow!!

Sub Sum_Stocklift_Column_MrExcel()

Dim lr As Long

' Find last row in column E with data
lr = Cells(Rows.Count, "E").End(xlUp).Row

' Place word "Total" in column E two rows down form last data row
Cells(lr + 2, "E").Value = "Total:"

' Place word "Expected WD Credit" in column E four rows down form last data row
Cells(lr + 4, "E").Value = "Expected WD Credit:"

' Place total at bottom of column F
Cells(lr + 2, "F").Formula = "=SUM(F19:F" & lr & ")"

End Sub

 

[Joe4]

You are welcome.
Glad I was able to help!