This is the sample dataset.
Step 1 – Creating a VBA Module to Clear Cells in Excel
- Go to the Developer tab and click Visual Basic.
You can also press ALT + F11 to open the “Microsoft Visual Basic for Applications” window.
- In “Insert”, select “Module’”.
Step 2 – Inserting a VBA Code in the Module
- In the“Module” window, enter this VBA code.
Code For Clearing Cells Keeping the Format:
Sub Clear_Cell_With_Button()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.ClearContents
End Sub
Code For Clearing Cells including Format
Sub Clear_Cell_With_Button_including_Format()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.Clear
End Sub
Code For Deleting Cells
Sub Delete_Cell_With_Button()
Dim rng As Range
Set rng = Application.InputBox(Prompt:="Select the Range of Cell", Title:="Exceldemy", Type:=8)
rng.Select
Selection.Delete
End Sub
Read More: Excel VBA to Clear Contents of Range
Difference Between Clear, Delete, and ClearContents Command in Excel VBA
The ClearContent command only clears the cell value while keeping the cell format. The Clear command removes both cell value and cell formatting and leaves the cells blank. The Delete command removes the cells and shifts the bottom cells up.
Step 3 – Create a Button to Clear Cells in Excel
- Go to the Developer tab.
- Click Insert and select Button.
- Draw a box in the area you want to place the button.
- The “Assign macro” window will open.
- Select the macro you created.
- Click OK.
- You will see a macro button in the selected area. Right-click the macro button to rename it as “Clear Contents Only”.
- Create two more buttons for the other codes named “Clear Cells Including Format” and “Delete Cells”
Step 4 – Running the VBA Macro to Clear Cells with a Button
- Click “Clear Contents Only” to clear the selected cell values only.
- A window will open. Select the range and click OK.
- The selected cell values are cleared but the format is the same.
- Click “Clear Cells Including Format” to clear cell values and remove formatting.
- Select the cell range that you want to clear and click OK.
The selected cells are cleared.
- Click “Delete Cells” and select the cell range.
The selected cells are removed and the bottom cells are shifted up.
Read More: How to Clear Contents of a Sheet with Excel VBA
Download Practice Workbook
Download the practice workbook here:
Related Articles
- How to Clear Contents Without Deleting Formulas Using VBA in Excel
- Excel VBA to Clear Contents of Named Range
- Excel VBA to Delete and Shift Left Any Cell or Column
- Excel VBA: Clear Contents If Cell Contains Specific Values
should select the cell range that i want to clear in the input box every time ?
Hello Alami,
Yes. you need to select the cell in the InputBox every time you want to clear the cells.
But if you want to avoid using InputBox, I’m giving you two different code to do so.
Mentioned the range then clear cells:
Select the cells then run the VBA code:
Regards
ExcelDemy