CODEWITHSUNDEEP
excelvbaexcel-formula
Gemma McCaughey
Gemma McCaughey

Reputation: 41

How to input formula to right of the last row

Please see below table, I want to put subtotals 1 rows down from the last row. This has worked well for row C and D (using the below code), however the rest of the columns wont always have info in every row. 

lastrow = Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(lastrow + 1, 3).Formula = "=sum(C1:C" & lastrow & ")"
lastrow = Cells(Rows.Count, "D").End(xlUp).Row + 1
Cells(lastrow + 1, 4).Formula = "=sum(D1:D" & lastrow & ")"

How can I insert a formula to the right of my other subtotals (highlighted in yellow) as I need them all on the same row?

Table

Upvotes: 0

Views: 346

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim LastRow As Long, LastColumn As Long, Column As Long

    With ThisWorkbook.Worksheets("Sheet1")

        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For Column = 3 To 10
            .Cells(LastRow + 2, Column) = Application.WorksheetFunction.Sum(.Range(.Cells(2, Column), Cells(LastRow, Column)))
        Next Column

    End With

End Sub

Upvotes: 0

urdearboy
urdearboy

Reputation: 14580

Just use one column to determine the last row (Using Column A here)

Also, the bottom of your sum range is indicated by the last row (LR), but where you want your formulas to go is 2 rows below (hence the LR + 2)

Dim ws as Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update
Dim LR as Long

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

ws.Range("C" & LR + 2).Formula = "=Sum(C2:C" & LR & ")"
ws.Range("D" & LR + 2).Formula = "=Sum(D2:D" & LR & ")"

Upvotes: 2

Related Questions