nested "with .../end with" statements
 

is it possible to nest "with... / end with" statements ?

nested "with .../end with" statements
 

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 :-)
----------------------------------------------------------------------------



"mark kubicki" wrote in message
...
is it possible to nest "with... / end with" statements ?




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.594 / Virus Database: 377 - Release Date: 24/02/2004

nested "with .../end with" statements
 

thnkx

"Ken Wright" wrote in message
...
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 :-)
--------------------------------------------------------------------------
--



"mark kubicki" wrote in message
...
is it possible to nest "with... / end with" statements ?




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.594 / Virus Database: 377 - Release Date: 24/02/2004