🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA Move Rows in Excel — Cut and Insert Without Overwriting (Not Cut and Paste)

|

VBA Move Rows in Excel — Cut and Insert Without Overwriting (Not Cut and Paste)

TL;DR — To move a row without wiping out data, you cut it and then Insert it — you do not paste it. Rows(5).Cut Destination:=Rows(20) drops the row on top of row 20 and overwrites whatever was there. Rows(5).Cut followed by Rows(20).Insert opens a gap at row 20, drops the row in, and closes the hole where it came from — nothing is destroyed. A move is a shift, so watch the trap: when you move a row down, the hole it leaves pulls every row below it — including your target — up by one.

Sub MoveRow()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    ws.Rows(5).Cut                 ' pick the row up (marching ants), source not gone yet
    ws.Rows(20).Insert Shift:=xlDown ' INSERT the cut row before row 20, shifting the rest down
    Application.CutCopyMode = False  ' clear the clipboard state when done
End Sub

Moving rows, moving columns, and the Cut verb underneath them are one idea: you pick data up and then choose how to put it down. Insert shifts a gap open and preserves everything; Paste lands on top and overwrites. Get the row case right — cut, insert, mind the shift — and moving columns is the same move turned ninety degrees, built on the same Range.Cut.

What you'll learn

  • The mental model — a move is cut and insert, and inserting is a shift, not a paste
  • The number-one bug — Cut Destination:= and paste overwrite the target and lose data
  • Why moving a row down renumbers everything below the source, so the target shifts up by one
  • How to move several rows at once without them landing in the wrong order (go bottom-up)
  • Why formula references follow a cut row instead of breaking into #REF! like a delete

The mental model: a move is a shift, not a paste

There are two motions hiding inside "move this row." First you pick it up — Rows(5).Cut puts the row on the clipboard in cut mode (the marching ants). Then you put it down, and this is where every data-loss story begins, because there are two completely different ways to do it:

  • Paste it (Cut Destination:=Rows(20), or a manual .Cut then .Paste) drops the row on top of row 20. Row 20's old contents are gone, silently, no warning.
  • Insert it (Rows(5).Cut then Rows(20).Insert) opens a fresh gap at row 20, drops the cut row into it, and closes the hole back at row 5. Everything else slides over to make room. Nothing is lost.

Moving a row is the second motion, always. It is the same shift you already know from inserting and deleting rows: the grid physically opens and closes, and everything below the change renumbers. Hold on to that — the whole article follows from "a move is a shift."

The number-one bug: pasting on top overwrites the target

This looks like it moves a row, and it is the most common way people destroy data in a macro:

ws.Rows(5).Cut Destination:=ws.Rows(20)    ' WRONG for a move - this PASTES onto row 20

Cut Destination:= is a paste. It copies row 5's cells onto row 20, overwriting row 20's data, and then blanks row 5. If row 20 held anything, it is gone. That is fine when row 20 is genuinely empty scratch space, but it is a disaster when you meant "slot this row in between the others."

The move that keeps your data is cut, then Insert:

ws.Rows(5).Cut                              ' pick it up
ws.Rows(20).Insert Shift:=xlDown            ' RIGHT - open a gap, drop it in, nothing overwritten

When the clipboard holds cut cells, .Insert performs an insert cut cells — it makes room instead of landing on top. The rule is blunt: to move, insert what you cut; never paste it. Reach for Cut Destination:= only when you truly want to overwrite an empty target.

The shift trap: moving down renumbers your target

Here is the surprise that fills help forums — "I cut a row, inserted it lower down, and all my data moved up." It is not a bug; it is the shift being consistent.

The cut and the insert happen as one operation. When you move row 5 down to row 20, the hole at row 5 closes as part of the same move, so rows 6 through 20 each slide up by one — and the row that was 20 is now sitting at 19. Your row lands relative to the shifted grid, not the row numbers you had in your head when you wrote 20.

The fix is to stop moving by hard-coded row numbers, because those numbers move under you. Locate the landmark by value and insert relative to it, so the shift cannot fool you:

Dim target As Range
Set target = ws.Columns("A").Find("Subtotal", LookAt:=xlWhole)   ' find WHERE, not a number
ws.Rows(5).Cut
target.EntireRow.Insert Shift:=xlDown        ' inserts above the real "Subtotal" row, shift or not
Application.CutCopyMode = False

If you must use numbers, remember the direction: moving up (source below target) leaves the target's number intact; moving down (source above target) shifts the target up by one. When in doubt, insert above a found landmark.

Moving several rows: go bottom-up

Move rows one at a time in a forward loop and each move renumbers the ones you have not touched yet, so the second move grabs the wrong row. This is the same discipline as deleting rows in a loop — process from the bottom up so the shifts happen below your cursor, where they cannot disturb rows you still have to move:

Dim i As Long
For i = lastRow To firstRow Step -1          ' bottom-up: shifts land below, not above
    If ws.Cells(i, "D").Value = "Archive" Then
        ws.Rows(i).Cut
        ws.Rows(archiveTop).Insert Shift:=xlDown
    End If
Next i
Application.CutCopyMode = False

For a contiguous block, move it in one shot — ws.Rows("5:9").Cut then a single .Insert — rather than looping five times and fighting five shifts. One cut, one insert, no renumbering surprises mid-loop.

Cut moves references; delete breaks them

There is one more reason Cut + Insert is the safe move: Excel follows it. A formula elsewhere that reads =Data!B5 is rewritten to point at the row's new home after the move, so it keeps reading the same data. That is the opposite of deleting a row, where a formula pointing into the deleted row collapses to =#REF! and stays broken. A move relocates the data and takes its references with it; a delete destroys the data and orphans them. When your goal is "the same numbers, a different place," that is exactly the behaviour you want.

How ExcelMaster helps

Moving a row is three decisions dressed up as one line — insert versus paste (data kept or destroyed), which direction the shift runs (and whether your target number still means what you think), and whether to move a block in one call or loop bottom-up — and getting any of them wrong fails quietly: a target row silently overwritten, rows landing one off, or half a loop moved into the wrong slots.

ExcelMaster lets you say what you want — "move every Archive row to the top of the archive block, keeping the data that is already there" — and it cuts and inserts instead of pasting, finds the landmark by value so the shift cannot fool it, and loops bottom-up when there are several. You keep the workbook and the code.

Frequently asked questions

How do I move a row in VBA without overwriting data?

Cut the row and then Insert it, do not paste it: ws.Rows(5).Cut followed by ws.Rows(20).Insert Shift:=xlDown. The Insert opens a gap at the destination and shifts the other rows down, so nothing is overwritten. Rows(5).Cut Destination:=Rows(20) pastes on top of row 20 and destroys its contents — use that only when the target is empty.

Why does my data move up after I cut and insert a row?

Because the move is a single shift. When you cut a row and insert it lower down, the hole the row leaves closes as part of the same operation, so every row below the source — including the one you aimed at — slides up by one. Insert relative to a landmark you find by value rather than a hard-coded row number, and the shift stops mattering.

How do I move multiple rows at once in VBA?

For a contiguous block, cut the whole range and insert once: ws.Rows("5:9").Cut then .Insert. For scattered rows selected by a condition, loop from the last row up to the first (Step -1) so each move shifts rows below your position, never the ones you still have to move.

How do I move a row to another worksheet?

Cut the row and insert it on the other sheet: ThisWorkbook.Worksheets("Data").Rows(5).Cut then ThisWorkbook.Worksheets("Archive").Rows(2).Insert Shift:=xlDown. It is the same cut-and-insert; only the destination sheet changes. Qualify both sheets so the macro does not depend on which one is active.

What is the difference between Cut and Copy when moving a row?

Cut moves — it empties the source once the row lands, so you end with one copy. Copy duplicates — the source stays, and you would have to delete it yourself, which is easy to get wrong. To move a row, use Cut; see VBA Cut for the full mechanics.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-09-17.

Related guides: VBA Cut · VBA Move Columns · VBA Insert Rows · VBA Delete Rows · VBA Copy Paste