shift cells right when deleting a cell in vba

Question

I need to shift cells to the right after I have deleted content. This option is not given by excel, I get only 4 choices: - Shift cells left - Shift cells up - Entire row - Entire column

At the end I wish to end-up with something lke this in my VBA code:

Selection.Delete Shift:=xlToRight

I changed from

Selection.Delete Shift:=xlToLeft

Thank you in advance for any help on this Brgds Patrick

I finally ended up with this and it works amzingly fine:

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

I needed the use of variables which are defined at top level, because the ranges I need to move to the right will never have the same number of columns.

I hope this will help others in future too. Thx to all for your replies

Answer 1

I prefer this simple aproach:

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

I agree with the comments it shouldn't be massively complicated but I think it is worth an answer. This preserves any formatting of the shifted cells (the ones to the left of the range) but clears the format and contents of the deleted cells.

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