vba - Cut & Paste Overwriting Range Object definitions -

i've found interesting problem excel vba's cut , paste involving use of defined range object.

here's code doesn't work:

sub pastetorangedoesntwork() dim strng range dim j, k, x, y integer  set strng = range("a3") x = 0 j = range(strng, strng.end(xltoright)).columns.count k = worksheetfunction.max(range(strng, strng.end(xldown)))  y = 1 k     while strng.offset(x, 0) = y         x = x + 1     wend     if y < k         range(strng.offset(x, 0), strng.end(xldown).offset(o, j - 1)).select         selection.cut         set strng = strng.offset(0, j + 1)         activesheet.paste destination:=strng         x = 0     end if next y end sub 

the problem when pasting defined strng, strng object disappears , becomes , undefined object.

there's simple fix, i've done below.

sub pastetorangeworks() dim strng range dim j, k, x, y integer  set strng = range("a3") x = 0 j = range(strng, strng.end(xltoright)).columns.count k = worksheetfunction.max(range(strng, strng.end(xldown)))  y = 1 k     while strng.offset(x, 0) = y         x = x + 1     wend     if y < k         range(strng.offset(x, 0), strng.end(xldown).offset(o, j - 1)).select         selection.cut         set strng = strng.offset(0, j)         activesheet.paste destination:=strng.offset(0, 1)         set strng = strng.offset(0, 1)         x = 0     end if next y end sub 

this works -- i.e. not pasting new cells directly strng , instead strng.offset(0,1), strng object remains defined.

the data in question 5 columns across. first column integer (with values going 1 7), next column text followed column dates , finally, 2 columns of general format data (2 decimal points).

the fix not difficult i'm perplexed why first code doesn't work. have ideas?

if use .paste method, defined ranges fall withing paste boundaries reset. exact "why?" microsoft can explain i'm afraid.

a better alternative work range.value , range.clear members; these won't cause issue, faster, , don't mess clipboard. note copies values , not formatting nor formulas.

the code can this:

  dim sourcerng range   set sourcerng = range(strng.offset(x, 0), strng.end(xldown).offset(0, j - 1))   set strng = strng.offset(0, j + 1)   dim destrng range   set destrng = strng.resize(sourcerng.rows.count, sourcerng.columns.count)   destrng.value = sourcerng.value   call sourcerng.clear