I set one range to another. When I delete the original range, the other is cleared. Why?

Question

I set one range to another:

'Save row info

Set rRow = loSrc.ListRows(lListRowIndex).Range

When I delete the source, rRow is cleared.

'Delete row from table
loSrc.ListRows(lListRowIndex).Delete

Why does this happen? This wouldn't happen if I wrote:

iNumber = iSource + 1
iSource is nothing

And even more importantly, how can I stop this from happening, so that I can use rRow even after loSrc.ListRows(lListRowIndex).Delete has executed.

+1 This is a very interesting question, I noticed this once but didn't find an answer then. — Goos van den Bekerom, Oct 28 '14 at 12:29
Could you please add some more info about the code? It would be great to have at least the definition of the variables involved (such as `loSrc`, `iNumber`, `iSource`). Anyway, I suspect you are referencing the Range object (so when it's deleted, also the object `rRow` gets so). — Matteo NNZ, Oct 28 '14 at 13:44
P.S.: the set keyword creates a reference to the object (source [here](http://stackoverflow.com/a/349636/3111149)) — Matteo NNZ, Oct 28 '14 at 13:48
Is there any way to create a copy of the object instead of a reference to it? When I remove the Set keyword I get an error message. — user1283776, Oct 28 '14 at 14:35

score 0 · Accepted Answer · answered Oct 28 '14 at 14:42

Matteo NNZs comment help me understand this.

The Set keywork creates a reference to the object. When I delete the object, the reference is also affected.

By using

Dim vRow() as variant

instead of

rRow as range

I was able to write:

vRow = loSrc.ListRows(lListRowIndex).Range
loSrc.ListRows(lListRowIndex).Delete

and have vRow retain its values

1 Answers1