excel - VBA Range variable gone after cut

Question

424 error. Would anyone be kind enough to tell me why the range variable was gone after cut?

with sheets(1)
Dim des as range
set des = .range("A15")
.range("A1:A3").cut des
msgbox(des.row+5)
end with

Answer 1

A Range object stores a reference to particular cells, not to particular address. When the referenced cells move around, the Range object will follow. E.g. if you store a Range("B1") and then insert a column between A and B, your variable will now have Range("C1"), because your B1 has moved to the right.

When the tracked cells cease to exist, the Range instance becomes unusable.

Cutting replaces the actual cells entirely as opposed to simply overwriting their contents, so the cell your Range is tracking stops to exist, and some other cell will now assume the address of A15. That is a different cell though, so your Range instance is gone.

A notable exception is when you destroy only a part of the Range. In that case the Range accepts the new cells and maintains its shape. E.g. if you stored Range("A15:A20") and cut Range("A1:A3") over A15, the resulting range would still be A15:A20.