Multiple Subtotals

bigfoot

Active Member
Joined
May 1, 2002
Messages
321
I've found postings on creating subtotals with unknown number of rows. However, I'm searching for help with vba, for a column with multiple groupings, seperated by a blank rows. once a blank row is encountered, total the quantity of the rows prior to the blank, and keep working down. i guess when it encounters multiple blanks, or perhaps a symbol, it would end that column and move on to the next column. i would also like to select the columns to be summed, since that would vary also. any help would be most appreciated. thanks
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

bigfoot

Active Member
Joined
May 1, 2002
Messages
321
thanks for response. a column could be inserted, used and deleted afterwards. since there could be 10-15 columns, it could get pretty big. thanks.
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
Hi. This function creates a subtotal formula in every empty row after a number of values and makes the text bold. If two subsequent empty rows are found, the function stops.

Code:
Sub createsubtotals()

    Dim startoflist As Range
    Dim m_Column As Integer
    Dim m_Row As Integer
    
    m_Column = 1
    m_Row = 1
    
    Do
        If Cells(m_Row, m_Column).Value <> "" Then
            If startoflist Is Nothing Then
                Set startoflist = Cells(m_Row, m_Column)
            End If
        Else
            If Not (startoflist Is Nothing) Then
                Cells(m_Row, m_Column).Formula = "=SUM(" & startoflist.Address & ":" & Cells(m_Row - 1, m_Column).Address & ")"
                Cells(m_Row, m_Column).Font.Bold = True
                Set startoflist = Nothing
            End If
        End If
        m_Row = m_Row + 1
    Loop While Not (Cells(m_Row, m_Column).Value = "" And Cells(m_Row - 1, m_Column).HasFormula = True)

End Sub
 

bigfoot

Active Member
Joined
May 1, 2002
Messages
321
works beautiful. many thanks. couple of questions please.
i noticed that it works on values, but not forumulas. any way to adjust this?
also, any way to input the number of columns sum, instead of one at a time?
regardless, thanks for your help.
 

bigfoot

Active Member
Joined
May 1, 2002
Messages
321
works beautiful. many thanks. couple of questions please.
i noticed that it works on values, but not forumulas. any way to adjust this?
also, any way to input the number of columns sum, instead of one at a time?
regardless, thanks for your help.
 

Forum statistics

Threads
1,141,095
Messages
5,704,310
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top