Avatar of llawrenceg
llawrencegFlag for United States of America asked on

Caculating subtotals with VBA

I need some VBA code to calculate the subtotals for each group in Column A that has a $$balance in column F  but with the subtotals 3 rows below  the colum total. There is a variable number of groups in Column A- can be up to twenty different group

Total  $204,347.21

Group 1 total:  $103854.21
Group 2 total:    $92358.52
Group 3 total:      $5409.29
Book1.xls
Microsoft DevelopmentMicrosoft Excel

Avatar of undefined
Last Comment
irudyk

8/22/2022 - Mon
Saurabh Singh Teotia


Enclosed is your workbook and code where before shows you your data before macro is run and after is once the macro is run..
Saurabh..
Sub subtotal()
Application.ScreenUpdating = False
 
Dim x As String
x = "Totals"
Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Delete
Dim rng As Range
Set rng = Range("A1:H" & Cells(65536, "a").End(xlUp).Row)
rng.subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
MsgBox "Done"
Range("A1").Select
 
Application.ScreenUpdating = True
 
End Sub

Open in new window

Subtotal.xls
ASKER
llawrenceg

Unfortunately not quite what I needed . The table can not be broken up into subtotals and the results of subtotaling each group can only show up below the totals
irudyk

I think you could use the SUMIF formula to do what your are looiking for. See the revised attached file for an example:
Book1.xls
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
llawrenceg

Since the column of data items will be of variable length i need some vab that will adjust to various lengths
irudyk

Not clear what you mean by "lengths".  If you mean that the number of data rows may increase or decrease, then you could modify the SUMIF formula to start at the heading row and end at the Total row.  If that is done, then the formulas for the group totals will expand and contract as rows are added/deleted.  See revised example file.
Book1.xls
ASKER
llawrenceg

Great work but I need it in VBA.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
llawrenceg

Also  he label or title for each group would need to be added
ASKER CERTIFIED SOLUTION
irudyk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question