Insert a Variable Row to SUM SubTotal

ChrisOK · #1 02-11-2018, 12:26 PM

Need the code to find the last row of content on ACTIVE SHEET, then jump down +1 to place Subtotals beneath all the columns that exist; Bolding them would be helpful.

Hoping it can be 'variable' in the sense it's knows where to place the subtotals regardless of the size of the content table... in other words, to work with various files as some sheets might have 5 columns and 50 rows... the next sheet to use it on might have 25 columns and 100 rows.. the code isn't fixed/restricted to a constant range..

FOR EXAMPLE:
If current active sheet has 10 columns
20 rows

1st row is a title row
so SUMS would appear on the 21st row in this scenario

I'm using this awesome code by Jindon to extract content from a master report and place it onto a new tab. Now, I realize I need to have a final action initiate the placement of subtotals..

Code:

Sub test()
    With Sheets("extd").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        .AutoFilter 14, "x"
        If .Columns(1).SpecialCells(12).Count > 1 Then
            .Copy Sheets.Add(after:=.Parent).Cells(1)
        End If
        .AutoFilter
        Columns.Autofit 'added this to size columns
    End With
End Sub

Small sample file attached:
There's a 3rd module I added to test other SubTotaling code but can't get it working - (not married to it - but if it's useful as a start - I left for viewing)

jeffreybrown · #2 02-11-2018, 02:52 PM

Here's something I picked up many years ago which might fit your needs...

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
End Sub

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

jeffreybrown · #4 02-12-2018, 10:46 AM

You are very welcome. Glad it worked out for you.

Just as an aside, you can make a slight adjustment to the added part. Very rarely do you ever have to select an object.

Code:

    With ActiveCell.EntireRow
        .Font.Bold = True
        .NumberFormat = "0.00"
        .Style = "Comma"
    End With

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