Help with Subtotaling within Macros!

[hosmurf]

Hi,

I'm writing my first macro, and I need some help. So I have my datase
selected> D1: the end of the data. I want to replicate the behavior o
the subtotal pop up (where it asks me "Add subtotal to" and then I ca
select each colum I want to see a subtotal for: Columns E1 through th
last column.

When I record the macro, I get this:

Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(15
16, 17 _
, 18, 19, 20, 21, 22), Replace:=True, PageBreaks:=False
SummaryBelowData:= _
True

I think the "TotalList" Thing is messing me up. How do I select th
Array from, let's say Column E through the end of the data? the numbe
of columns is variable.

Darn it, this is not making sense... anyone understand what i'
saying?

Explaining problems in excel through text is really difficult.


Example:

Name | Jan |Feb |Mar |
Jon | 5 | 10 |4 |
Jane | 5 | 4 |3 |
Jon | 5 | 10 |4 |
Jon | 4 | 13 |4 |

Result:
Name | Jan |Feb |Mar |
Jon | 14 | 33 |12 |
Jane | 5 | 4 |3 |

I want the macro code to help me subtotal (with the group by Name), al
the months. The months will always be variable. Some times I will hav
to subtotal Jan -Mar, and Others Jan -Dec, etc..


Thanks for reading.
Jon :confused

 

[Dave Peterson]

I'm guessing you apply Data|subtotals to a range of data that includes A1
through last used column in row 1--and the last used row in column D.

Option Explicit
Sub testme01()
Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim wks As Worksheet
Dim iCtr As Long
Dim myList() As Variant

Set wks = Worksheets("sheet1")
With wks
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("a1", .Cells(LastRow, LastCol))

ReDim myList(5 To LastCol)
For iCtr = 5 To LastCol
myList(iCtr) = iCtr
Next iCtr

With myRng
Application.DisplayAlerts = False
.Subtotal GroupBy:=.Columns(4).Column, Function:=xlSum, _
TotalList:=myList, _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Application.DisplayAlerts = True
End With
.UsedRange.Columns.AutoFit
End With

End Sub