Insert, Delete & Clear: Rows, Columns, and Cells in VBA

• Your First Experience in the VBA Environment

As you begin to write code some of the most common tasks you will find yourself doing are inserting, deleting and clearing ranges of cells.

Deleting a range completely removes a range of cells from your workbook (both contents and formatting). The other cells on the sheet will shift as a result of the cell being deleted.

Clearing Contents is the equivalent of pressing the delete key while a cell is selected. It clears the characters contained in the cell while retaining the actual cell and its fromatting.

Range("A1").Cells.ClearContents

When deleting a range you have the choice of shifting the remaining cells on the sheet to the left or up.

Delete Range, Shift Up

Range("A1").delete(xlShiftUp)

Delete Range, Shift Left

Range("A1").delete(xlShiftUp)

When inserting a range you have the choice of shifting the other cells on the sheet to right or down.

Insert Range, Shift Cells Down

Range("A1").insert(xlShiftDown)

Insert Range, Shift Cells Right

Range("A1").insert(xlShiftToRight)

Row("1:1").EntireRow.ClearContents

Rows("1:1").EntireRow.Delete

Note: Deleting a row will shift the rows below up.

Row("1:1").EntireRow.Insert

Note: Inserting a row will shift the rows below down.

Columns("A:A").EntireColumn.ClearContents

Columns("A:A").EntireColumn.Delete

Note: Inserting a column will shift the columns to the left.

Columns("A:A").EntireColumn.Insert

Note: Inserting a column with shift columns to the right.

Now that we have learned some of the basics we are ready to perform repetitive tasks. The next module explains how to create a loop. Click here when you are ready to proceed.