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

VBA: Clear Contents, Delete and Insert Cells in Excel

Updated on September 4, 2012

This module will teach you how to perform some of the most common tasks involving cells from within the Microsoft Excel user interface and from within VBA. After completing this module you should be able to clear, delete, and insert new cells manually and programmatically.

Clearing Vs Deleting

Clearing: Clearing a cell deletes the cells contents but leaves the cell and its formatting intact.

Deleting: Deleting a cell completely removes the cell and its formatting from the sheet. When a cell is deleted one of the following actions must coincide.

  • Cells below must shift up, or
  • Cells to the right must shift to the left

See below for illustration

Clearing a Cell

To clear a cell simply select the cell and press the "delete" key.

Deleting a Cell

To delete a cell right click on the cell and select delete.

You will then need to tell excel whether to shift the cells up or to the left.

Inserting a Cell

To insert a cell right click on a cell and click insert. When a cell is deleted one of the following actions must coincide.

  • Cells below must shift down, or
  • Cells to right must shift to the right.

See below illustration

Visual Basic


Clear a Cell

Range("A1").clearcontents


Delete a Cell and Shift to the Left

Range("A1").insert(xlShiftToLeft)


Delete a Cell and Shift Up

Range("A1").delete(xlShiftUp)


Insert a Cell and Shift to the Right

Range("A1").insert(xlShiftToRight)


Insert a Cell and Shift Down

Range("A1").insert(xlShiftDown)


Test Data for VBA Macro

Sample Macro

Sub test()
Range("B1").Delete (xlShiftToLeft)
Range("A2").Insert (xlShiftToRight)
Range("B3").Delete (xlShiftUp)
Range("B4").Delete (xlShiftToLeft)
Range("B5").Delete (xlShiftUp)
End Sub

The End Result

Ready to Proceed?

In the next module we will learn to select ranges in Excel and VBA. Click here when you are ready to proceed.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      me 3 years ago

      "Delete a Cell and Shift to the Left

      Range("A1").insert(xlShiftToLeft)"

      Shouldn't it be "Range("A1").DELETE(xlShiftToLeft)" ? Not insert.

    • jonhaus profile image
      Author

      jonhaus 3 years ago from Kansas City, Mo

      Yes thanks for catching that

    Click to Rate This Article