Why are my Range variables changed by "Cut"

[TonyJollans]

Hi All,

Whilst looking at a problem in another thread I came across something I can’t explain. Can somebody else, please?

Copying a range from one sheet to another in the same workbook doesn’t cause me any problem. Both displays in the code below give Sheet1 $A$7 Sheet1 $A$12 as I would expect.

 Dim tjStart As Range
Dim tjEnd As Range

Set tjStart = Worksheets("Sheet1").Range("A7")
Set tjEnd = Worksheets(“Sheet1”).Range("A12")

MsgBox tjStart.Worksheet.Name & “ “ & tjStart.Address & " " & tjEnd.Worksheet.Name & “ “ & tjEnd.Address

Range(tjStart, tjEnd).

Copy

 Destination:=Worksheets("Sheet2").Range("A1")

MsgBox tjStart.Worksheet.Name & “ “ & tjStart.Address & " " & tjEnd.Worksheet.Name & “ “ & tjEnd.Address

Cutting the same range causes my Range variables to be changed to reflect the cell range from the destination and the sheet from the source. This time the first display gives Sheet1 $A$7 Sheet1 $A$12 as before but the second gives Sheet1 $A$1 Sheet1 $A$6.

 Dim tjStart As Range
Dim tjEnd As Range

Set tjStart = Worksheets("Sheet1").Range("A7")
Set tjEnd = Worksheets(“Sheet1”).Range("A12")

MsgBox tjStart.Worksheet.Name & “ “ & tjStart.Address & " " & tjEnd.Worksheet.Name & “ “ & tjEnd.Address

Range(tjStart, tjEnd).

Cut

 Destination:=Worksheets("Sheet2").Range("A1")

MsgBox tjStart.Worksheet.Name & “ “ & tjStart.Address & " " & tjEnd.Worksheet.Name & “ “ & tjEnd.Address

If that’s just the way it works I can live with it (I’ll have to live with it) but I can’t find anything to suggest so. What am I doing wrong?

Thanks,
Tony

 

[jonscott8]

That's just the way it works. If you have a reference to a range object that is cut, the reference is redirected to the range that is relative A1 as the original range was relative to the range that was cut. For instance, if you have a reference to C5, and range B46 is deleted, the reference to C5 will now be a reference to B2.

Personally, since a cut operation is simply a copy/delete, I would think the behavior would be the same as when you have a refence to a range that gets deleted, ie, the object reference is no longer valid.

A workaround to get the behavior I believe you desire, is to use the below snipped instead of the internal cut method.

Range(tjStart, tjEnd).Copy Destination:=Worksheets("Sheet2&quot.Range("A1&quot
Range(tjStart, tjEnd).ClearContents

Jon Hawkins

 

[TonyJollans]

Thanks Jon,

I did fear that was just the way it was but rather hoped not. Coding a workaround is not a problem; it's knowing when I need to.

Cut just removes data from cells; it does not remove the cells and I can't see any reason to change the reference. I somewhat naïevely thought that my private variables were under my own control - seems not. All part of the rich learning experience provided by Micro$oft.

Enjoy,
Tony