That array doesn't have to be 0 based or 1 based. You can actually just use the
columns you want:
Option Explicit
Sub testme()
With Worksheets("Summary")
Call SubTotalSummaryData(.Cells(1, .Columns.Count).End(xlToLeft).Column)
End With
End Sub
Private Sub SubTotalSummaryData(LasDateCol As Long)
Dim DataArray() As Long
Dim i As Long
With Worksheets("Summary")
' Populate array of all used columns of data- used by
' the Subtotal function.
ReDim DataArray(3 To LasDateCol)
For i = 3 To LasDateCol
DataArray(i) = i
Next i
.UsedRange.Subtotal GroupBy:=1, _
Function:=xlSum, TotalList:=Array(DataArray()), _
Replace:=True, PageBreaks:=False, _
SummaryBelowData:=True
End With
End Sub
I changed the Byte's to Long's.
Thief_ wrote:
I'm trying to automate a Subtotal function, ie, I want to SUM the data
in columns C onwards for each change in column A:
Code:
Private Sub SubTotalSummaryData(LasDateCol As Byte)
Dim DataArray() As Byte, cnt As Byte, i As Byte
With Worksheets("Summary")
' Populate array of all used columns of data- used by the
Subtotal function.
ReDim DataArray(LasDateCol - 2)
cnt = 0
For i = 3 To LasDateCol
DataArray(cnt) = i
cnt = cnt + 1
Next i
.UsedRange.Subtotal GroupBy:=1, Function:=xlSum,
TotalList:=Array(DataArray()), Replace:=True, PageBreaks:=False,
SummaryBelowData _
:=True
End With
End Sub
The number of columns present will vary each time the procedure is
called thus I tried creating an array of all the used columns via the
FOR....NEXT loop, but VBA doesn't like it.
Any ideas?
--
|
+-- Julian
|
--
Dave Peterson
|