Sub-total questions

[Guest]

Hi everyone, I have some questions regarding Sub-Totals:

I have a macro to insert sub-totals on a spreadsheet with multiple columns.
I want to Sum the values in most columns, Count the values in Column 7, and
Average the values in Columns 9, 16, and 18. My code looks like this:

Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(10, 11,
12 _
, 13, 14, 15, 17, 19, 20), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlCount, TotalList:=Array(7), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(9,
16 _
, 18), Replace:=False, PageBreaks:=False, SummaryBelowData:= True

However, I would like to show the Subtotal Sums, Counts, and Averages on the
same SubTotal row, not on separate rows. How can I do this? The workaround
I am currently using is to Sum all columns first, then select Column 7 and do
a search and replace on the subtotal Sum formula to change it to Count, then
use the same process to change Columns 9, 16, and 18 to Average. This seems
cumbersome, is there a cleaner way to do this?

2 follow-up questions:

1) Can I format the entire row with the Subtotals to be in bold font, as
opposed to just the row heading “Column 3 Total†being in bold?

2) Can I format the Count Sub-Total values for Column 7 to be in number
format with commas and no decimal places? The data in Column 7 is in a
Custom Format with leading zeroes, but I do not want the Count Sub-Totals to
be in that format.

 

[Dave Peterson]

I like that way of changing the =subtotal() parms. (I don't know of a different
way.)

#2 & #3. Use the outlining symbols to the left to hide the details.
Select the range you want formatted
Edit|goto|special|visible cells only
format those visible cells.

Record a couple of macros when you do it manually and you'll have your code.

 

[Guest]

Your workaround sounds pretty good to me. Perhaps you are not making the
changes efficiently.

Columns(7).Replace What:="subtotal(9,", _
Replacement:="subtotal(2,", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
' Handle your formatting questions:
With Columns(7).specialcells(xlformulas,xlNumbers)
.Numberformat = "#,###"
.EntireRow.Font.bold = True
end With
Range("I:I,P,R:R").Entirecolumn.Replace _
What:="subtotal(9,", _
Replacement:="subtotal(1,", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False

 

[Guest]

Thanks Dave and Tom for validating my workaround for changing the Subtotal
calcs. It works for me, I just always assumed there was a more efficient way.

Your answers to the formattting questions were extremly helpful. A couple
of additional formatting questions if I may:

I am layering 3 sets of Sub-totals on my spreadsheet, for Criteria1,
Criteria2, and Criteria3. (Criteria3 is a subset of Criteria2, Criteria2 is
a subset of Criteria1.) As a result, I get Sub-totals for each of the 3
criterias which I want, but I also get 3 Grand Totals. I only want 1 Grand
Total, preferably for Criteria1. How do I eliminate the other 2 Grand Totals.

Also, because this spreadhseet is a template and I copy in a different
amount of records each time, I find that after Sub-totalling I often have a
lot of additional blank rows appear after the last row of data and before the
Grand Totals. How can I eliminate these blank rows?

 

[Dave Peterson]

I think you're letting excel guess what the last row of your data is. If you're
more careful with what range gets subtotaled, I bet those blanks go away.

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
with .range("a1:x" & lastrow)
'your subtotal code here


====

I'm not sure I understand the grandtotal question. I only get one grand total
line.

But if you know where the lines to delete are, you can just eliminate them with
your code.

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.rows(lastrow-1).resize(2).delete
....or...
.rows(lastrow-2).resize(2).delete
or whatever you want.

 

[Guest]

Based on what you have said you might be better off with a pivot table. Based
on your description it will do everything you want and more. If you want a
further expanation or some help with pivot tables let me know...

As for the Grand Totals that is just the way subtotals work. You can delete
them after the sub-totals are created, but the sub totals will create them no
matter what you do (pivot tables won't). To get rid of the blank rows you
need a macro similar to this...

range(Cells(rows.count, "A"), _
Cells(rows.count, "A").end(xlup).offset(1, 0)).entirerow.delete
'The cells will not actually be gone until you save the spreadsheet