Deleting Cells and Rows in ExcelVBA

The following VBA procedure demonstrates how to delete specific cell ranges from a worksheet:

Sub DeleteCells()
    ' Activate the worksheet named "Tabelle1"
    ThisWorkbook.Worksheets("Sheet1").Activate
    ' Delete entire rows 6 and 7
    Range("6:7").Delete
    ' Delete cells A2 and A3, shifting the remaining cells upward
    Range("A2:A3").Delete Shift:=xlShiftUp
End Sub

Detailed Explanation:

The Delete Method

The Delete method of the Range object is used to remove cells, rows, or columns from a worksheet.

Optional Parameter: Shift

The Shift parameter defines how Excel rearranges neighboring cells after the deletion:

• xlShiftUp: shifts remaining cells upward to fill the gap
• xlShiftToLeft: shifts remaining cells left to fill the gap

If the Shift parameter is omitted, Excel determines the shifting direction based on the shape of the range:

• If the range is taller than wide, cells are typically shifted up.
• If the range is wider than tall, cells are typically shifted left.

What This Procedure Does:

1. Activates the worksheet named « Tabelle1 ».
2. Deletes entire rows 6 and 7 using:

3. Range(« 6:7 »).Delete

Since full rows are selected, all rows below them are automatically shifted upward. The Shift parameter is not needed here.

1. Deletes the cells in range A2:A3 using:

5. Range(« A2:A3 »).Delete Shift:=xlShiftUp

This removes the two vertical cells and shifts the cells below them upward to fill the space.
Alternatively, Shift:=xlShiftToLeft would shift the neighboring cells to the left instead — useful for horizontal ranges.

Additional Notes:

• To delete entire rows from a cell range:

• Range(« A2:A3 »).EntireRow.Delete

This would remove rows 2 and 3 entirely.

• To delete entire columns:

• Range(« A2:A3 »).EntireColumn.Delete

This would delete column A.

• This procedure effectively reverses the changes made by the previous insertion procedure (ZelleEinfuegen), returning the worksheet to its original structure.

Summary

• Use .Delete to remove cells, rows, or columns.
• Use Shift to control whether neighboring cells move up or left.
• For full rows or columns, Excel handles shifting automatically, and the Shift parameter is unnecessary.