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