Thread: move rows without copy/paste clipboard

Hello Jevni,

I was looking to avoid interfering with the user's clipboard. If something were stored on it, and this code ran, it would clear the user's info just so I could just move some cells. I figured there always seems to be a better way to directly reference the excel object model rather than resorting to the clipboard or selections.

For example, I do not use .Select, as generated by the "Recorder", to write info to cells:

    Range("C4").Select
    ActiveCell.FormulaR1C1 = "Hello World"
    Range("C5").Select

I would use:

    Range("C12").Value = "Hello World"

Or not use copy/paste to copy info

    Range("C5").Select
    Selection.Copy
    Range("C8").Select
    ActiveSheet.Paste

I would use:

    Range("C8").Value = Range("C5").Value

I was hoping there was a way to move one range to the location of another range of the same size. Something like:

    MyRange1.cut Destination:=MyRange2 Shift:=xlDown

This works without the "Shift:=xlDown", but certainly not with it.

It's not a critical thing for my code to live without, I just thought there was a more "proper" way to move info around the object model than using the clipboard.

That's defiantly something I should use to clean up with after I'm done, thanks shg and jevni.

But I suppose there is no real way to move rows around without the use of the clipboard? I suppose even when I set the CutCopyMode to False, it still killed what was on the clipboard from before the code ran?

No big deal:

sub tst()
  sheets(1).rows(6).resize(5).insert
  sheets(1).rows(6).resize(5)=sheets(1).rows(16).resize(5).value
  sheets(1).rows(16).resize(5).delete
End Sub

Wow,

Very cool, I'll use that! Thanks for the help!

Ah, I'll make to sure to be cognizant of references to those cells. This problem would also exist if I use the copy/cut method correct?

For the cell's formulas, the below code seems to work

sheets(1).rows(6).resize(5)=sheets(1).rows(16).resize(5).Formula

I think this is the more solid type of code I was looking for.

Mucho thanks yall!