CODEWITHSUNDEEP
excelvba
LittleAuditorium
LittleAuditorium

Reputation: 37

AutoSum at bottom of column

I am trying to have a macro auto-sum the bottom of column L each time I run it while it takes into account that the length of the column varies. I had this code that auto-summed the bottom of a column G, so I switched the G to an L but it is not working as intended. Why is that? Could someone please make an edit to the code so it automatically sums the bottom of column even though the range may vary weekly?

Sheets("Report").Select
    Const SourceRange = "A:L"
    Dim NumRange As Range, formulaCell As Range
    Dim SumAddr As String
    Dim c As Long

    For Each NumRange In Columns(SourceRange).SpecialCells(xlConstants, xlNumbers).Areas
        SumAddr = NumRange.Address(False, False)
        Set formulaCell = NumRange.Offset(NumRange.Count, 0).Resize(1, 1)
        formulaCell.Formula = "=SUM(" & SumAddr & ")"

        c = NumRange.Count
    Next NumRange

Upvotes: 0

Views: 836

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

This would add a SUM total at the bottom of each column between A & L.

Public Sub Add_Total()

    Dim ColumnNumber As Long
    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Report")
        For ColumnNumber = 1 To 12
            LastRow = .Cells(.Rows.Count, ColumnNumber).End(xlUp).Row
            With .Cells(LastRow + 1, ColumnNumber)
                .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
                .Font.Bold = True
            End With
        Next ColumnNumber
    End With

End Sub

To add it to just column L you could change the code to:

Public Sub Add_Total1()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Report")
        LastRow = .Cells(.Rows.Count, 12).End(xlUp).Row
            With .Cells(LastRow + 1, 12)
                .FormulaR1C1 = "=SUM(R2C:R[-1]C)"
                .Font.Bold = True
            End With
    End With

End Sub

Upvotes: 1

Jim
Jim

Reputation: 79

I don't have enough reputation yet to add a comment to reply to your question to Darren - but all you have to do is delete the whole line with "Next" in it from his second set of code; it was the end of the "for" loop that he removed from his first set of code and should have been deleted.

Upvotes: 1

Related Questions