在删除VBA中的单元时，请直接移动细胞

Question

我需要在删除内容后向右移动单元格。此选项不是由Excel提供的，我只有4个选择： - 向左移动细胞 - 向上移动细胞- 整行 - 整列

最后，我希望在我的vba代码中以此为例：

Selection.Delete Shift:=xlToRight

我从

更改

Selection.Delete Shift:=xlToLeft

事先感谢您对此的任何帮助brgds帕特里克

我终于最终得到了这个，它效果很好：

Sub ShiftRight()
 Selection.End(xlToRight).Select
 numcol = ActiveCell.Column
 numcol2 = numcol
 numcol = (numcol - lngColNumber) + 5
 strcolletter = Split(Cells(1, numcol - 1).Address, "$")(1)
 strcolletter2 = Split(Cells(1, numcol2).Address, "$")(1)
 Range(Myrange).Select
 Selection.Cut Destination:=Columns(strcolletter & ":" & strcolletter2)
End Sub

我需要使用最高级别定义的变量，因为我需要向右移动的范围永远不会具有相同数量的列。

我希望这也将来也能帮助其他人。全部回复您的答复

Answer 1

我更喜欢这种简单的掠夺：

Sub DELETE_MOVE_TO_RIGHT()
Dim firstcolumn As Integer
Dim lastcolumn As Integer
Dim firstrow As Integer
Dim lastrow As Long
Dim i As Integer
Dim j As Integer
Dim nrows As Long
Dim ncols As Integer
ncols = Selection.Columns.Count
nrows = Selection.Rows.Count
firstcolumn = Selection.Column
lastcolumn = firstcolumn + ncols - 1
firstrow = Selection.Row
lastrow = firstrow + nrows - 1
    Range(Cells(firstrow, firstcolumn), Cells(lastrow, lastcolumn)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft
    For j = lastcolumn To firstcolumn + 1 Step -1
        Range(Cells(firstrow, firstcolumn), Cells(lastrow, firstcolumn)).Cut
        Range(Cells(firstrow, j + 1), Cells(lastrow, j + 1)).Insert Shift:=xlToRight
    Next j
End Sub

Answer 2

我同意这些评论，它不应该很复杂，但我认为这是一个值得的答案。这保留了移动的单元格的任何格式（范围左侧的单元格），但清除了已删除的单元格的格式和内容。

Sub DelRight()
Dim firstColumn, lastColumn, firstRow, lastRow, nRows, nCols, colsToShift As Long
Dim sheet As Worksheet
Dim rangeToCopy, rangeToReplace, rangeToDelete As Range
Set sheet = ActiveSheet
firstColumn = Selection.Column
nCols = Selection.Columns.Count
nRows = Selection.Rows.Count
lastColumn = firstColumn + nCols - 1
colsToShift = firstColumn - 1
firstRow = Selection.Row
lastRow = firstRow + nRows - 1
' Shift cells to left of the range to the right hand end of the range
With sheet
    If firstColumn > 1 Then
        Set rangeToCopy = .Range(.Cells(firstRow, 1), .Cells(lastRow, colsToShift))
        Set rangeToReplace = .Range(.Cells(firstRow, lastColumn - colsToShift + 1), .Cells(lastRow, lastColumn))
        rangeToCopy.Copy destination:=rangeToReplace
    End If
    ' Delete cells to the left of the shifted cells
    Set rangeToDelete = .Range(.Cells(firstRow, 1), .Cells(lastRow, lastColumn - colsToShift))
    rangeToDelete.ClearContents
    rangeToDelete.ClearFormats
End With
End Sub