Microsoft Office Forums - View Single Post - [Solved] Insert a Variable Row to SUM SubTotal

ChrisOK · 02-12-2018, 08:41 AM

Thanks Jeffrey! - Love the simplicity of it -- and yes, it does the trick!
I'll add a couple rows to make it select that last line and format the raw totals with BOLD, COMMA and NUMBER format to distinguish it as a SubTotal row..
LOVE IT! Thank you again!

Here's what I added to the end; may be of help to others needing help with a similar solution..

Code:

Public Sub SumDemo()
    Dim ColSumRng As Range, hdr As Variant
    Dim FirstEmptyRow As Long, SubTot As Long

    ' Goto each column header starting with column B
    For Each hdr In Range("A1:L1").SpecialCells(xlCellTypeConstants, 3)

        ' Find where data ends for the specific Column
        FirstEmptyRow = Cells(65536, hdr.Column).End(xlUp).Row + 1

        ' Subtotal all numbers for Column
        SubTot = Application.WorksheetFunction.Subtotal(9, Range(hdr.EntireColumn.Address))

        ' Put Subtotal at bottom of column
        Cells(FirstEmptyRow, hdr.Column).Value = SubTot
    Next hdr
    
    Range("A1").Select
    Selection.End(xlDown).Select
    
ActiveCell.EntireRow.Select
    Selection.Font.Bold = True
    Selection.NumberFormat = "0.00"
    Selection.Style = "Comma"
    Range("A1").Select
    
End Sub