Clear Cell Contents in Excel with VBALearn how to use VBA to clear contents in Excel
Excel’s VBA feature allows you, the user, to enter code to execute commands that you normally can’t do with its functions.
You can also use it to create macros to ease the repetition of tasks.
One of the things you can do with VBA is to clear cell contents.
If you frequently work with data in Excel, you’ll know adding and deleting data are tasks that you’ll do from time to time.
You may only have to clear the contents of a single. Sometimes, you may have to clear the contents in a range of cells.
Or you may have to clear the contents of an entire worksheet, or even the entire workbook.
You can do all of the above tasks with VBA. In this article, I’ll be showing you how to use VBA to clear cell contents in Excel.
Be it a single cell, a range of cells, a worksheet, or an entire workbook, you can do it all with VBA.
Let’s get started.
(Note: this article assumes that you already know how to use the VBA feature)
Clear, Delete, ClearContents
There are three methods that you can use in VBA to clear a cell’s contents.
These are the Clear, Delete, and ClearCotents methods. Let’s go over quickly what each method does.
The Delete Method
The Delete method in VBA not only clears a cell’s contents but also deletes the cell (or range of cells) entirely.
Then, it replaces the deleted cells or range of cells with the cells below or to the right of it.
The direction of how the cells shift depends on the shape of the range. Here’s a visual representation of what it does:
The code to run the Delete method in VBA is as follows:
Sub name()
Range(range).Delete
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to delete
In our illustration above, the code I used is this:
The Clear Method
Unlike the Delete method, the Clear method in VBA only clears the content in the cell (or range of cells). It does not delete the cell itself.
Note though that it clears everything in the cell, not just the content.
This means that the Clear method also removes the formatting from the cell (e.g. font, font size, font color, borders, cell fill, etc.). Here’s a visual representation of what it does:
The code to run the Clear method in VBA is as follows:
Sub name()
Range(range).Clear
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to clear
In our illustration above, the code I used is this:
The ClearContents Method
Unlike the Clear method, the ClearContents method in VBA only clears the content in a cell and nothing else.
This means that it retains the formatting of the cell.
This is the ideal method if you only want to clear a cell without having to affect its formatting.
Here’s a visual representation of what it does:
The code to run the ClearContents method in VBA is as follows:
Sub name()
Range(range).ClearContents
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to clear content from
In our illustration above, the code I used is this:
Clear Contents From a Cell or Range of Cells
If you only want to clear the contents in a single or a range of cells, you may use the following VBA code:
Sub name()
Range(range).ClearContents
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to clear
For example, let’s say that I want to clear the content in cell A1 of this sheet:
The code that I’ll be using will be this:
And the result is this:
Now, if I want to clear the contents from the range A1:B5, the code I’ll be using will be this:
And the result is this:
Note that this code applies to the active worksheet.
(From this point on, we’ll be using the ClearContents method for illustrations. If you instead want to use the Delete or Clear methods, just replace ClearContents in the code with the method of your choice.)
Clear Contents From the Active Worksheet
If you want to clear all cell contents from the entire active worksheet, then the code you have to use is this:
Sub name()
Cells.ClearContents
End Sub
Where:
name – refers to the description of the method
Let’s try to use this code on the following sheet:
The code we’ll be using to clear the cell contents from this sheet is as follows:
And the result is this:
All the contents from the entire sheet are cleared (without affecting the formatting).
Clear Contents From a Specific Worksheet
If you want to clear contents from a specific worksheet rather than just the active worksheet, you use the following codes:
To clear contents from a cell or range of cells:
Sub name()
Worksheets(sheet_name).Range(range).ClearContents
End Sub
To clear contents from the entire sheet:
Sub name()
Worksheets(sheet_name).Cells.ClearContents
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to clear
sheet_name – refers to the name of the sheet that you clear contents from
For example, I want to clear the contents from a sheet named “SampleSheet”. I want to clear all cell contents. Thus, the code I’ll use is this:
Running this code will clear all contents from the sheet even if it isn’t the active sheet.
Clear Contents From the Entire Workbook
If you want to clear contents from the active workbook, you’ll have to include a For Each loop in the code.
You may use the following codes depending on when you want to clear contents from a specific range or the entire sheet.
To clear contents from a cell or range of cells in all sheets of the active workbook:
Sub name()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Range(range).ClearContents
Next Ws
End Sub
Where:
name – refers to the description of the method
range – refers to the cell or range of cells that you want to clear
For example, if you want to clear the contents in the cell A1 of all sheets in the active workbook, the code you have to use is the following:
To clear contents from a cell or range of cells in all sheets of the active workbook:
Sub name()
Dim Ws As Worksheet
For Each Ws In ActiveWorkbook.Worksheets
Ws.Cells.ClearContents
Next Ws
End Sub
Where:
name – refers to the description of the method
For example, if you want to clear the contents of all sheets in the active workbook, the code you have to use is the following:
Other Clear-related Methods in VBA
We already know the Clear and ClearContents methods. Here are the other related methods you can use:
ClearComments – this clears all comments
ClearFormats – this clears the cell formatting
ClearHyperlinks – this clears hyperlinks
ClearNotes – this clears all notes
ClearOutline – this clears outlines
Conclusion
And those are the ways you can clear contents in Excel with VBA.
Which of the above methods do you prefer the most?
Let me know in the comments.