CODEWITHSUNDEEP
c#excel
Master
Master

Reputation: 2153

How to cut a range of cells using Excel Interop

I have a column and i'm looking to cut rows 3-37 and move it to the column directly to the right of it.

I tried the following and it moves the values correctly to the right but the cell styles don't copy over.

Range copyRange = ws1.Range["L5:L37"];
Range insertRange = ws1.Range["M5:M37"];
insertRange = copyRange;

I've also tried solution from here Cut and Paste Columns in Excel Range with c# but nothing seem to copy

Range copyRange = ws1.Range["L5:L37"];
Range insertRange = ws1.Range["M5:M37"];
//insertRange.Insert(copyRange.Cut());
insertRange.Insert(XlInsertShiftDirection.xlShiftToRight, copyRange.Cut());

Upvotes: 0

Views: 781

Answers (1)

Sophie Swett
Sophie Swett

Reputation: 3390

If you want to just replace the contents of the destination cells with the contents of the source cells, and delete the contents of the source cells, then the Range.Cut method ought to do the trick if you call it with the destination range as an argument:

Range sourceRange = ws1.Range["L5:L37"];
Range destinationRange = ws1.Range["M5:M37"];
sourceRange.Cut(destinationRange);

This will not copy sourceRange to the clipboard, but it will clear the clipboard if you already have a range on the clipboard.

You can also specify only the upper left cell of the destination range, and it will do the same thing:

Range sourceRange = ws1.Range["L5:L37"];
Range destinationRange = ws1.Range["M5"];
sourceRange.Cut(destinationRange);

If you want to keep the contents of the source cells, use Copy instead of Cut.

(Note that the statement insertRange = copyRange; definitely should never copy any cells anywhere if insertRange is a local variable. That could only happen if C# supported overloaded assignment (which, to my knowledge, it does not) or if some other code somehow "sensed" the assignment.)

Upvotes: 1

Related Questions