CODEWITHSUNDEEP
vbaexcel
user1283776
user1283776

Reputation: 21764

Cut + Insert sometimes deletes and shifts cells, other times it doesn't. Why?

I have the following code for cutting and pasting a row:

'Cut range from source row
Dim lSourceRow As Long
Dim lSourceStartColumn As Long
Dim lSourceEndColumn As Long
lSourceRow = t.Row
lSourceStartColumn = loSource.Range.Column
lSourceEndColumn = loSource.Range.Column + loSource.ListColumns.Count - 1
wksSource.Range(wksSource.Cells(lSourceRow, lSourceStartColumn), wksSource.Cells(lSourceRow, lSourceEndColumn)).Cut

'Select target worksheet for insert method
wksTarget.Activate

'Insert range into target row
Dim lTargetRow As Long
Dim lTargetStartColumn As Long
Dim lTargetEndColumn As Long
lTargetRow = loTarget.DataBodyRange.Row + loTarget.ListRows.Count
lTargetStartColumn = loTarget.Range.Column
lTargetEndColumn = loTarget.Range.Column + loTarget.ListColumns.Count - 1
wksTarget.Range(Cells(lTargetRow, lTargetStartColumn), Cells(lTargetRow, lTargetEndColumn)).Insert

'Strange thing that original row is deleted if source and target are on the same sheet, otherwisw not
If Not wksSource Is wksTarget Then
    wksSource.Range(wksSource.Cells(lSourceRow, lSourceStartColumn), wksSource.Cells(lSourceRow, lSourceEndColumn)).Delete
End If

When I cut and paste within the same sheet, the cut cells are deleted and the cells below them are shifted up.

When I cut and paste to another sheet, the cut cells are not deleted, instead they are left blank.

This seems inconsistent to me. Is there anything I can do to make VBA delete the cut cells and shift cells up in both cases?

Upvotes: 1

Views: 461

Answers (1)

Jason Faulkner
Jason Faulkner

Reputation: 6558

Unfortunately, Excel doesn't do this natively so your current approach of manually removing it afterwards is a required step to accomplish what you need.

As a suggestion, you can try setting a Range variable to the area you are moving (cut/pasting) which will prevent you from having to duplicate the selection logic. This may make the code more readable and the workaround a bit more palatable:

Dim moveCells As Range
Set moveCells = wksSource.Range(wksSource.Cells(lSourceRow, lSourceStartColumn), wksSource.Cells(lSourceRow, lSourceEndColumn))
moveCells.Cut

[...] ' All other code remains unchanged.

If Not wksSource Is wksTarget Then
    ' Excel doesn't shift the cut cells when pasting to an alternate sheet.
    ' Must manually do this.
    moveCells.Delete
End If

Upvotes: 1

Related Questions