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

VBA: Clear Contents, Delete and Insert Ranges in Excel

Updated on September 5, 2012

Clearing Vs Deleting

Click edit above to add content to this empty capsule.

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 the range must shift up, or
  • Cells to the right of the range must shift to the left

See below for illustration

Clearing a Range

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

Deleting a Range

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

Deleting Range "C3:H16"

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

Inserting a Range

To insert a range of cells right click on a range and click insert.

When a range is inserted 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 Code


Clear a Range

Range("C3:H16").ClearContents


Insert a Range and Shift Cells Right

Range("C3:H16").insert(xlShifttoRight)

Insert a Range and Shift Cells Down

Range("C3:H16").insert(xlShiftDown)


Delete a Range and Shift Cells Up

Range("C3:H16").delete(xlShiftUp)

Delete a Range and Shift Cells Up

Rnage("C3:H16").delete(xlShiftLeft)

Ready to Proceed?

In the next module we will cover changing the font, bolding, italicizing and underlining of ranges of cells from Excel and VBA. Click here when you are ready to proceed.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.