How to Delete Cells in Excel VBA
In Excel VBA, you can use the Range
object and its Delete
method to delete cells or ranges.
Example 1: Deleting a Single Cell
To delete a single cell, you need to specify the cell’s address using the Range
object and then call the Delete
method.
Sub DeleteSingleCell()
Dim rng As Range
Set rng = Range("A1") ' Change the address as per your requirement
rng.Delete
End Sub
In this example, the code deletes cell A1. If you run this code, the content of cell A1 will be deleted, and the cells below it will shift up to fill the empty space.
Example 2: Deleting a Range of Cells
If you want to delete a range of cells, you can again use the Range
object and the Delete
method.
Sub DeleteCellRange()
Dim rng As Range
Set rng = Range("A1:C3") ' Change the range as per your requirement
rng.Delete
End Sub
In this example, the code deletes cells A1 to C3. Running this code will clear the content of all the cells in the specified range and move the cells below the range up to fill the empty space.
Example 3: Deleting Entire Rows or Columns
In addition to deleting individual cells or ranges, you can also delete entire rows or columns using the Delete
method. To delete rows, use the EntireRow
property of the Range
object, and similarly, use the EntireColumn
property to delete columns.
Sub DeleteRowsAndColumns()
Dim rngRows As Range
Dim rngColumns As Range
' Deleting rows
Set rngRows = Range("2:4") ' Change the range to the rows you want to delete
rngRows.EntireRow.Delete
' Deleting columns
Set rngColumns = Range("C:E") ' Change the range to the columns you want to delete
rngColumns.EntireColumn.Delete
End Sub
In this example, the code deletes rows 2 to 4 and columns C to E. Running this code will remove the specified rows and columns, shifting the remaining rows and columns accordingly.