Clear Range problem

[Jac Tremblay]

Hi,
I have this simple code but somehow, it doesn't work properly. There must be
some simple solution but I cannot figure it out.
I want to determine a specific range on a particular sheet and clear its
contents before inserting something else. I have created a test workbook to
illustrate the problem. Here is the code:
' *********************************************************
Sub UnionTest()
Dim wbk As Workbook
Dim sht As Worksheet
Dim rngStart As Range
Dim rngTemp As Excel.Range
Dim intI As Integer
Set wbk = ActiveWorkbook
For intI = 1 To 3
Set sht = wbk.Sheets(intI)
Set rngStart = sht.Range("B2")
Set rngTemp = Union( _
Range(rngStart.Offset(3, -1).Address & _
":" & rngStart.Offset(6, 1).Address), _
Range(rngStart.Offset(10, -1).Address & _
":" & rngStart.Offset(15, 1).Address))
MsgBox "sht.Name = " & sht.Name & vbCrLf & _
"rngTemp.Address = " & rngTemp.Address
sht.Activate
rngTemp.Select
rngTemp.ClearContents
Next intI
Set rngTemp = Nothing
Set rngStart = Nothing
Set sht = Nothing
Set wbk = Nothing
End Sub
' *********************************************************
Instructions for debug only:
sht.Activate
rngTemp.Select
I hope someone can pinpoint the problem easily.
Thanks.

 

[Jacob Skaria]

Jac

Try the below. The sheet was not refereed to for the below line .
Set rngTemp = Union( sht.Range(rngStart.Offset(3, -1).Address & _)


Sub UnionTest()
Dim wbk As Workbook
Dim sht As Worksheet
Dim rngStart As Range
Dim rngTemp As Excel.Range
Dim intI As Integer
Set wbk = ActiveWorkbook
For intI = 1 To 3
Set sht = wbk.Sheets(intI)
Set rngStart = sht.Range("B2")
Set rngTemp = Union( _
sht.Range(rngStart.Offset(3, -1).Address & _
":" & rngStart.Offset(6, 1).Address), _
sht.Range(rngStart.Offset(10, -1).Address & _
":" & rngStart.Offset(15, 1).Address))
MsgBox "sht.Name = " & sht.Name & vbCrLf & _
"rngTemp.Address = " & rngTemp.Address
sht.Activate
rngTemp.Select
rngTemp.ClearContents
Next intI
Set rngTemp = Nothing
Set rngStart = Nothing
Set sht = Nothing
Set wbk = Nothing
End Sub

 

[Jac Tremblay]

Hi Jacob,
This solves my problem all right. Thanks you for your precious comment. It
is very appreciated.
Have a good day.