Insert a Variable Row to SUM SubTotal

ChrisOK · #3 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I want to create a bar chart of multiple variable. Then I need to draw trend lin of those variable	shimulsiddiquee	Excel	1	05-16-2017 07:39 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Run-time error 91 object variable or with block variable not set	JUST ME	Word VBA	4	03-25-2014 06:56 AM
Run-time error '91': Object variable or With block variable not set	tinfanide	Excel Programming	2	06-10-2012 10:17 AM
Each subtotal signature require only at last page for each subtotal.	aligahk06	Excel	0	05-07-2010 11:07 PM