CODEWITHSUNDEEP
vbaexcel
twigg
twigg

Reputation: 3993

Excel VBA Subtotals leaves a large gap

Im running Excel 2003 still new to VBA so something obvious I'm sure. I have a spreadsheet that lists a number of unit models, who owns the unit and which 'team' of people who (Team 1, team 2 ect) own the unit and I want to get a subtotal when sorting by team, I'm using this code:

Sub Subtotals()
    Rows.Select
    Selection.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Now this works perfect at counting the units by team and inserting subtotals for each team but for some reason the last team and the grand total are about 300 rows down from the last entry. Why is it doing this?

Upvotes: 2

Views: 2133

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

The reason this is unpredicatable is the unqualified Rows.Select

You need to specify the range you really want to apply subtotals to, eg:

Sub Subtotals()
    Dim rng as range

    Set rng = '  the range you want
    rng.Subtotal GroupBy:=4, Function:=xlCount, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

There are many ways of specifying a range in VBA, programmatic and manual. Which one to use depends mostly on your sheet layout.

Upvotes: 1

Related Questions