- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: Clear Contents, Delete and Insert Ranges in Excel
Prerequisite Courses
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.