problem with Subtotals and ClearOutline method

J

jacqui

I am using the following code to generate subtotals. The
code is

Sub Sub_Totals()

Dim arr() As Integer
Dim i As Integer
Dim colArray As Variant

Sheets("data").Range("DatArea").Select

FirstCol = 8
LastCol = Range("IV1").End(xlToLeft).Column
ReDim arr(FirstCol To LastCol)
For i = FirstCol To LastCol
arr(i) = i
Next
colArray = arr()
Selection.Subtotal groupby:=4, Function:=xlSum,
totallist:=colArray, _
Replace:=True
Selection.Subtotal groupby:=6, Function:=xlSum,
totallist:=colArray, _
Replace:=False

With Selection

.ClearOutline

End With


After VBA has created the subtotals, the Grand Totals are
outside of my "datarea" range so that when I use selection
to activate the clearOutline method it doesn't work
properly (the expand/contract lines are removed but the
showlevel buttons remain). In effect I'm only selecting a
subset of my data albeit everything apart from 3 lines out
of 2000. Can anyone help me resolve this?
PS, does anyone know the syntax to format the subtotals so
that they appear in a bold font as well.
PPS, any feedback on the code I'm using is welcome 'cause
it's taking about 1 to 2 minutes to process 100 columns by
about 1500 rows.
Many thanks
Jacqui
 
Top