How To Delete Cells In Excel Vba

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.

Similar post

Leave a comment