ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

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

Updated on September 17, 2012

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 vs. Clearing

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.


Clearing a Range

Range("A1").Cells.ClearContents


Deleting a Range

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)


Inserting A Range

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)


Clearing a Row

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


Deleting a Row

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

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


Inserting a Row

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

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


Clearing a Column

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


Deleting a Columns

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

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


Inserting a Column

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

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



Ready to Proceed?

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.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.