Search code examples
excelvbacountsubtotal

Subtotal and Count in Same Row Macro

I have a report that I am trying to generate that shows loans made outside of policy, grouped by loan officer, with subtotals of dollar amounts of loans made outside of policy. I would also like to have a count of the number of loans made, but I cannot get the count to appear on the same line as the subtotal. 

 Range("A2:K2", ActiveCell.End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1, 7, 8), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(4), _
    Replace:=False, PageBreaks:=False, SummaryBelowData:=True

This results in the subtotals that I want in columns 1, 7 and 8, but a line is inserted above the subtotals and the count is presented in column 4 one row above the subtotals. I have tried different variations in the true/false statements at the end. I also tried to add a "dummy" column of 1s next to each loan, but I then want to hide the "dummy" column so the report will fit on one sheet of paper, and I don't know how to offset the resulting subtotal count, since the number of loans made outside of policy by various loan officers will vary between periods. Is there a way to do this?

Solution

  • As pnuts solved in the comments above, for my report to have the subtotals in the appropriate columns (1, 7 and 8 in this case) and the count in the appropriate column (column 4 in this case), the following code works beautifully:

    Range("A2:K2", ActiveCell.End(xlDown)).Select
Selection.CurrentRegion.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(1, 7, 8), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Columns(4).Replace What:="subtotal(9", Replacement:="subtotal(3", LookAt:=xlPart

    Remember that if you want to count ALL the cells in a range, use the CountA function: "subtotal(3". If you are only requiring a count of cells that contain numbers (e.g., excluding logical values, text, error values, etc.,) then use the Count function: "subtotal(2".

    Thanks again to pnuts for giving the correct answer!