Clearing a Worksheet Through VBA: The Cells.Clear Statement

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:

  1. Data in cells
  2. Formatted cells 
    1. Background color
    2. Font face
    3. Font style like bold / italic / underline
    4. Font color
    5. Cell borders ( various styles)
    6. Conditional formatting based on cell contents
  3. Shapes
  4. Buttons
  5. Form objects like text boxes, check boxes, radio buttons
  6. Data validation with a list
  7. Formulas in cells
  8. Pivot tables
  9. Graphs
  10. Charts
  11. Connections to external databases
  12. Comments
  13. Hyperlinks
  14. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *