“剪切+插入"有时会删除和移动单元格，而其他时候则不会.为什么? [英] 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.

这似乎与我不一致.在这两种情况下，我都可以做些什么使VBA删除剪切的单元格并向上移动单元格吗?

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?

推荐答案

不幸的是，Excel并不是本机执行此操作，因此您当前需要的手动删除它的方法是完成所需操作的必要步骤.

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.

作为建议，您可以尝试在要移动的区域(剪切/粘贴)设置一个 Range 变量，这将避免您不得不重复选择逻辑.这可以使代码更易读，并且解决方法也更可口:

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

这篇关于“剪切+插入"有时会删除和移动单元格，而其他时候则不会.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！