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