Sub Total

[Norman Belanger]

Hi,

I created a macro to sort records, obtain a sub total then a grand total.
Easily done when creating a macro using the SubTotal function but to run the
macro for a new sheet holding different row count by row label, the subtotal
occurs at the wrong row break. I don't use VBA in Excel much but need a
simple way to do the following:

Name Number

Mark 1
Mark 1
Mark 1
Sub Total 3

Dan 2
Dan 2
Dan 2
Sub Total 6

Joyce 3
Joyce 3
Joyce 3
Sub Total 9

If I use the macro to run the following, it breaks it as:

Mark 1
Mark 1
Mark 1
Sub Total 3

Mark 1
Sub Total 1

Thanks

Norm

 

[Dave Peterson]

Sometimes you can just change your range to point at the whole column instead of
specifying a starting row and an ending row.

ActiveSheet.Columns("A:E").Subtotal GroupBy:=1, Function:=xlCount, _
TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

or if you can pick out a column that always has data:

dim myRng as range
with activesheet
set myrng = .range("a1:e" & .cells(.rows.count,"A").end(xlup).row)
end with

myrng.subtotal.....