0

I need to write an Excel Macro that sums up the values of the cells above in a certain column, e.g. column "C". The first Row is always 5 but the number of rows that comes below that is varying. The code needs to select the cell in the last row and then sum up the values above, e.g. (=SUM(C5:C?)) in that cell.

Here is what I already have from code but I have trouble specifiying the second last cell in the column, which is the last cell that is going to be included in the sum:

With ThisWorkbook
Set wsDestination = .Worksheets("Overview")

End With

FirstRow = Rows(5).EntireRow
lastRow = wsDestination.Cells(Rows.Count, "C").End(xlUp).Row

Worksheets("Overview").Select
wsDestination.Cells(lastRow, "C").Select
ActiveCell.Formula = "=SUM(FirstRowC:(C& LastRow).Offset(-1,0))"

Can someone explain to me how to specify this? Thanks

BigBen
  • 46,229
  • 7
  • 24
  • 40
Meisje17
  • 19
  • 4
  • `"=SUM(C5:C" & LastRow & ")"`... though you need to write the formula in the next row under last row. – BigBen Sep 10 '20 at 13:30
  • Hi Ben, thanks for the help. Unfortunately, I need to insert the formula in the last used row, as this is a row for totals. – Meisje17 Sep 10 '20 at 13:39
  • That would mean you are overwriting data. In that case, use `LastRow - 1` in the formula. – BigBen Sep 10 '20 at 13:40
  • it gives me a expected: end of statement error ```"=SUM(C5:C"& LastRow-1&")"``` when I put this – Meisje17 Sep 10 '20 at 13:51
  • You've got the spaces wrong... should be a space before and after each `&`. – BigBen Sep 10 '20 at 13:52

0 Answers0