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.
--
Jac Tremblay

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


--
If this post helps click Yes
---------------
Jacob Skaria


"Jac Tremblay" wrote:

> 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.
> --
> Jac Tremblay

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


"Jacob Skaria" wrote:

> 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
>
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Jac Tremblay" wrote:
>
> > 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.
> > --
> > Jac Tremblay