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

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 = FalseDim x As Stringx = "Totals"Cells.Find(What:=x, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).ActivateActiveCell.EntireRow.DeleteDim rng As RangeSet rng = Range("A1:H" & Cells(65536, "a").End(xlUp).Row)rng.subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7), Replace:=True, PageBreaks:=False, SummaryBelowData:=TrueMsgBox "Done"Range("A1").SelectApplication.ScreenUpdating = TrueEnd Sub

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

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.

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..

Open in new window