[Dave Peterson[_3_]]

Always keep column 8 up to the lastcolumn -1?

If yes, how about something like:

Option Explicit
Sub testme()
Dim LastCol As Long
Dim vaper() As Long
Dim iCtr As Long
Dim usedCtr As Long
With Worksheets("sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

If LastCol < 10 Then
MsgBox "error--in layout!"
Exit Sub
Else
ReDim vaper(1 To LastCol - 9)
vaper(1) = 8
For iCtr = 10 To LastCol - 1
vaper(iCtr - 8) = iCtr
Next iCtr
End If

End Sub

I used row 1 to get the lastused column. It should be pretty similar if you get
it some other way.

Graham Slade wrote:

I have a question about programmatically defining the
contents of an Array constant used for subtotal parameter.

My current code looks like this:
' Create an array for the columns to perform subtotals on
vaper1 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18)
vaper2 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22)
vaper3 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26)
If Int((lEndPer - lStartPer + 1) / 4) = 2 Then
vaPer = vaper1
Else
If Int((lEndPer - lStartPer + 1) / 4) = 3 Then
vaPer = vaper2
Else
vaPer = vaper3
End If
End If
With Worksheets("Sheet1").Range("B1")
.Subtotal groupBy:=1, Function:=xlSum, _
totalList:=vaPer, Replace:=True
End With

The purpose of the code is to insert subtotals on all the
columns up to the last column with data in it. The last
column is defined by the user during run-time so the
totalList parameter of the Subtotal method is not fixed.

My code is the best workaround I could manage to get me
over the first few months of running this, but I wonder if
anyone has a more general and elegant solution that would
work for any number of columns the user defined?

Any help appreciated.

Regards,

Graham

--

Dave Peterson

[Graham Slade]

Dave,

Sorted perfectly. As usual it's always obvious once you've seen the answer.

Many thanks,

Graham


"Dave Peterson" wrote in message
...
Always keep column 8 up to the lastcolumn -1?

If yes, how about something like:

Option Explicit
Sub testme()
Dim LastCol As Long
Dim vaper() As Long
Dim iCtr As Long
Dim usedCtr As Long
With Worksheets("sheet1")
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

If LastCol < 10 Then
MsgBox "error--in layout!"
Exit Sub
Else
ReDim vaper(1 To LastCol - 9)
vaper(1) = 8
For iCtr = 10 To LastCol - 1
vaper(iCtr - 8) = iCtr
Next iCtr
End If

End Sub

I used row 1 to get the lastused column. It should be pretty similar if
you get
it some other way.

Graham Slade wrote:

I have a question about programmatically defining the
contents of an Array constant used for subtotal parameter.

My current code looks like this:
' Create an array for the columns to perform subtotals on
vaper1 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18)
vaper2 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22)
vaper3 = Array(8, 10, 11, 12, 13, 14, 15, 16, 17, 18,
19, 20, 21, 22, 23, 24, 25, 26)
If Int((lEndPer - lStartPer + 1) / 4) = 2 Then
vaPer = vaper1
Else
If Int((lEndPer - lStartPer + 1) / 4) = 3 Then
vaPer = vaper2
Else
vaPer = vaper3
End If
End If
With Worksheets("Sheet1").Range("B1")
.Subtotal groupBy:=1, Function:=xlSum, _
totalList:=vaPer, Replace:=True
End With

The purpose of the code is to insert subtotals on all the
columns up to the last column with data in it. The last
column is defined by the user during run-time so the
totalList parameter of the Subtotal method is not fixed.

My code is the best workaround I could manage to get me
over the first few months of running this, but I wonder if
anyone has a more general and elegant solution that would
work for any number of columns the user defined?

Any help appreciated.

Regards,

Graham

--

Dave Peterson