Clearing a Worksheet Through VBA: The Cells.Clear Statement
Contents
Component of Excel Sheets
Excel sheets are a part of an Excel workbook or a document. There can be any number of Excel sheets in a workbook. A Excel sheet may or may not have any of the below components/contents:
- Data in cells
- Formatted cells
- Background color
- Font face
- Font style like bold / italic / underline
- Font color
- Cell borders ( various styles)
- Conditional formatting based on cell contents
- Shapes
- Buttons
- Form objects like text boxes, check boxes, radio buttons
- Data validation with a list
- Formulas in cells
- Pivot tables
- Graphs
- Charts
- Connections to external databases
- Comments
- Hyperlinks
- Formatted tables, etc…
Clearing the Contents of a Worksheet
In case we need to clear contents, formatting, and other data from one or many sheets based on some condition, it might be tiring to do it manually. In that case, it would great if we could clean up the sheet using a pre-defined program. Here is where VBA comes to our rescue.
Clearing Only Cell Contents
Syntax
[Sheets(“<sheet name>”)].Cells.ClearContents
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearContents
This statement clears only the data (text) in all cells of the sheet named “Sheet1.” It would still retain the other components of the sheet such as charts, graphs, format of cells etc.
Clearing Only Cell Format
Syntax
[Sheets(“<sheet name>”)].Cells.ClearFormats
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearFormats
This statement clears only the formatting of all cells of the sheet named “Sheet1.” It would still retain the data and other components of the sheet.
Delete a Sheet’s Used Range
Syntax
Activesheet.UsedRange.Delete
This statement has the capacity to delete all contents and objects of the active sheet within the used range.
Clear the Comments in a Worksheet
Syntax
Sheets(“Sheet1”).Cells.ClearComments
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearComments
This statement would clear up only the comments of all cells in the sheet named “Sheet1.” It would still retain the data, formatting, and other components of the sheet.
Clear the Hyperlinks in a Worksheet
Syntax
Sheets(“Sheet1”).Cells.ClearHyperlinks
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearHyperlinks
This statement would clear up only the hyperlinks of all cells in the sheet named “Sheet1.” It would still retain the data, formatting, comments, and other components of the sheet.
Clear the Outline in a Worksheet
Syntax
Sheets(“Sheet1”).Cells.ClearOutline
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearOutline
This statement would clear up only the Outline of all cells in the sheet named “Sheet1.” It would still retain the data, formatting, comments, and other components of the sheet.
Clear the Notes in a Worksheet
Syntax
Sheets(“Sheet1”).Cells.ClearNotes
Where the sheet name is optional and if not provided, the active and open sheet is considered.
Example
Sheets(“Sheet1”).Cells.ClearNotes
This statement would clear up only the notes on all cells in the sheet named “Sheet1.” It would still retain the data, formatting, comments, and other components of the sheet.
Clearing an Entire Sheet
If all the content including data, format, shapes, and other objects need to be cleared, the clear property of the cells (object in worksheet) can be used.
Syntax
[Sheets(“<sheet name>”)].Cells.Clear
Where the sheet name is the name of the worksheet on which the action needs to be done. Cells refer to all the cells of the sheet.
In the place of the sheet’s name within double quotes, we can also provide the index number of the sheet without double quotes
“Activesheet” can also be used in the place of the sheet’s name.
Example:
Sheets(“Sheet2”).Cells.Clear Activesheet.Cells.Clear
Let’s imagine that we need to clear all comments / notes / hyperlinks from 3 sheets that contain the work “Unit”. The code below can help us get the task done quickly.
Note: This can be done on any number of sheets.
One sheet has some data and borders in it.
The next sheet named Unit 2 has a comment in one of the cells.
There is one more sheet named “unit 15” that has some data with conditional formatting.
Now we use this VBA code to clear all these in the three sheets.
This image also shows the other clearing options which I explained above.
Sub clear_all_demo()
' declare all required variables
Dim worksheet_count As Integer
Dim iterator As Integer
Dim ws_name As String
Dim ws As Worksheet
' Set worksheet_count equal to the number of worksheets in the active or open workbook.
worksheet_count = ActiveWorkbook.Worksheets.Count
' Start the loop.
For iterator = 1 To worksheet_count
' find the name of the worksheet
Set ws = ActiveWorkbook.Worksheets(iterator)
ws_name = ws.name
' check if it has the word "unit" in it
If InStr(ws_name, "Unit") > 0 Then
' clear all contents of the sheet
ws.Cells.Clear
End If
Next iterator
End Sub
Code Explained:
Here we declare necessary variables and initialize values for workbook, worksheet, counter, etc.
Then we enter into a loop. Inside the loop, we place a condition to check if the name of the worksheet contains the word “Unit” in it. If so, we clear all contents of the sheet.
On running the code, we see that all kinds of data are cleared from all the three forms.
Conclusion
VBA directly reduces the time and effort of clearing any specific or all kinds of data (list components of a worksheet) easily in no time. But it is worthwhile to say that we need to be very cautious before running the script on the VBA editor as this can be undone/not reversible (i.e) data once lost, is lost forever. The only possibility to save ourselves from such a situation is to make a backup of the workbook before trying anything on the VBA editor.