M
mark kubicki
is it possible to nest "with... / end with" statements ?
K
Ken Wright
This was a previously posted routine to create subtotals automatically. Note
the nested 'With' near the end
Sub GetSubTotals()
Dim lrow As Long
Dim lcol As Long
Dim i As Integer
Dim MyArray() As Integer
lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
ReDim MyArray(2 To lcol)
For i = 2 To lcol
MyArray(i) = i
Next i
With Range(Cells(1, 1), Cells(lrow, lcol))
.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=MyArray, Replace:=True,
_
PageBreaks:=False, SummaryBelowData:=True
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Range("A1").Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A1,7)=""Average"""
With .FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Interior.ColorIndex = 19
End With
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
the nested 'With' near the end
Sub GetSubTotals()
Dim lrow As Long
Dim lcol As Long
Dim i As Integer
Dim MyArray() As Integer
lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
ReDim MyArray(2 To lcol)
For i = 2 To lcol
MyArray(i) = i
Next i
With Range(Cells(1, 1), Cells(lrow, lcol))
.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=MyArray, Replace:=True,
_
PageBreaks:=False, SummaryBelowData:=True
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Range("A1").Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A1,7)=""Average"""
With .FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Interior.ColorIndex = 19
End With
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
M
mark kubicki
thnkx
Ken Wright said:This was a previously posted routine to create subtotals automatically. Note
the nested 'With' near the end
Sub GetSubTotals()
Dim lrow As Long
Dim lcol As Long
Dim i As Integer
Dim MyArray() As Integer
lcol = ActiveSheet.UsedRange.Column - 1 + _
ActiveSheet.UsedRange.Columns.Count
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
ReDim MyArray(2 To lcol)
For i = 2 To lcol
MyArray(i) = i
Next i
With Range(Cells(1, 1), Cells(lrow, lcol))
.Subtotal GroupBy:=1, Function:=xlAverage, TotalList:=MyArray, Replace:=True,
_
PageBreaks:=False, SummaryBelowData:=True
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Range("A1").Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RIGHT($A1,7)=""Average"""
With .FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Interior.ColorIndex = 19
End With
End With
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission
-------------------------------------------------------------------------- --
