2

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?

twigg
  • 3,753
  • 13
  • 54
  • 96

1 Answers1

1

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.

chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • ok that makes sense but the data is different everyday so I can't say the range will always be ("A1:D75") because it wont be the same amount of data everyday – twigg Feb 08 '12 at 11:19
  • @twigg Then you should use `Set rng = ActiveSheet.Cells(1).CurrentRegion`. – GSerg Feb 08 '12 at 14:00
  • @twigg: Alternatively you can see how to get the exact range in this post. http://stackoverflow.com/questions/9204688/how-to-put-query-results-into-a-datatable-with-excel-vba-and-ado – Siddharth Rout Feb 10 '12 at 04:27