Clear Sheet Using VBA in Excel

With VBA, you can quickly clear part of the worksheet or the entire worksheet in Excel using the Cells.Clear method.

It also gives you the flexibility to choose whether you want to clear everything or specific things such as the formatting or the content, etc.

Let’s see a couple of simple VBA code examples to clear a sheet in Excel.

Clear the Entire Active Sheet

Sub ClearActiveSheet()

    ' Clear everything from the active sheet
    ActiveSheet.Cells.Clear

End Sub

The Cells property in the above code refers to all the cells in the sheet, and the Clear method effectively removes everything from these cells, leaving you with a completely blank sheet.

It clears all the data, formatting, and formulas from the sheet that is currently active when you run the macro.

Now, let me show you how to clear specific things from the worksheet (such as only the content formatting or comments) using VBA.

Clear Only the Content

Sub ClearActiveSheetContent()

    ActiveSheet.Cells.ClearContents

End Sub

The above VBA code will remove any data or formulas in these cells in the entire sheet without affecting the cell formatting.

This macro is helpful when you need to reset the data in a sheet but want to preserve the existing formatting, like cell colors, borders, or number formats.

Clear Only the Format

Sub ClearActiveSheetFormats()

    ActiveSheet.Cells.ClearFormats

End Sub

The above VBA macro code would remove the formatting while leaving everything else intact, including the content and formulas in the cells.

This macro is particularly useful when you need to reset the visual appearance of a sheet without losing the data or calculations it contains.

Clear Only the Comments / Notes

Excel has notes as well as comments.

Notes (which earlier used to be called Comments are the non-threaded single notes, as shown below.

Comment is a threaded discussion where you can add a comment, and other people can reply in the same thread (as shown below).

If you want to clear all notes as well as comments from the entire sheet, you can use the below code:

Sub ClearActiveSheetComments()

    ActiveSheet.Cells.ClearComments

End Sub

Note: You can also use ClearNotes instead of ClearComments and it would work the same way

Also read: Comments in Excel VBA (Add, Remove, Block Commenting)

Clear Only the Hyperlinks

If you have hyperlinks in your worksheet and you want to clear all the hyperlinks in one go, you can use the below code.

Sub ClearActiveSheetHyperlinks()

    ActiveSheet.Cells.ClearHyperlinks

End Sub

Note that this is not going to remove the content of the cell or even change the formatting, but it would remove the hyperlink property of the cell, which means that the cell would no longer be clickable.

Also read: VBA to Remove Duplicate Values in Excel

Clear Only the Outline

If you have used the Group feature or the Auto Outline feature in Excel to create outlines, you can use the below code to clear the outline from the entire worksheet:

Sub ClearActiveSheetHyperlinks()

    ActiveSheet.Cells.ClearOutline

End Sub

Note: In all the codes covered so far, I’ve shown you how to clear the entire active sheet by using the expression ActiveSheet.Cells. If you want to clear a specific range or any specific worksheet, all you need to do is specify that range instead of ActiveSheet.Cells and use the rest of the code as is. I have covered some examples of this in the below sections

Also read: VBA Delete Sheet

Clear UsedRange in the Active Sheet

Sub ClearUsedRange()

    ActiveSheet.UsedRange.Clear

End Sub

This VBA code clears all content and formatting from the ‘UsedRange’ in the currently active worksheet of the active workbook.

‘UseRange’ refers to any part of the worksheet that has been used or altered in some way, such as cells containing data, formatting, or formulas.

Also read: VBA Create New Sheet (Sheets.Add)

Clear Sheet By Name

Below is the code to clear a sheet by using its name:

Sub ClearUsedRange()

    Worksheets("Sheet1").Cells.Clear

End Sub

In the above code, I have specified Sheet1 as the name of the worksheet, and then all the cells in this worksheet are cleared.

This approach is useful when you know the name of the sheet you want to clear and you don’t necessarily want it to be the active or currently selected sheet.

Also read: VBA Rename Sheet in Excel

Clear All the Sheets in the Workbook

Below is the VBA code that would clear all the sheets in the workbook in which the code is run:

Sub ClearAllSheets()

    Dim ws As Worksheet

    ' Loop through each worksheet and clear all cells
    For Each ws In ThisWorkbook.Worksheets
        ws.Cells.Clear
    Next ws

End Sub

The above macro uses the For Each Next loop to go through each worksheet in ThisWorkbook object and clear all the cells in each worksheet.

It is useful when you need to reset an entire workbook, clearing all sheets at once. It’s efficient and saves time compared to clearing each sheet individually.

Also read: VBA Protect / Unprotect Sheet

Clear Sheets That Contains a Specific Word in the Name

Below is the VBA macro code that would clear only those worksheets that have the word ‘Sales’ in their name.

Sub ClearSheetsContainingSales()

    Dim ws As Worksheet

    ' Loop through each worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets

        ' If sheet name contains the word "Sales", clear it
        If InStr(1, ws.Name, "Sales", vbTextCompare) > 0 Then
            ws.Cells.Clear
        End If

    Next ws

End Sub

The above code uses a For Next VBA loop to go through each worksheet in the workbook.

Inside the loop, the InStr function checks if the word “Sales” is part of the worksheet’s name (ws.Name). The vbTextCompare argument makes the comparison case-insensitive.

If the worksheet has the word ‘Sales’ in its name, ws.Cells.Clear method clears all the cells in it.

Clear a Protected Sheet

In the below code, I clear a protected sheet named ‘Sales’

Sub ClearProtectedSheet()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sales")  

    ' Unprotect the sheet (if it has a password, include it in the parentheses)
    ws.Unprotect "ThePassword" 

    ' Clear everything from the sheet
    ws.Cells.Clear

End Sub

The above code clears all contents from a protected worksheet named “Sales” in the active workbook.

It first unprotects this sheet using the provided password (“ThePassword“). Once unprotected, it uses the Cells.Clear method to remove all data and formatting from every cell in the worksheet.

Other Excel VBA articles you may also like:

• What is VBA in Excel?
• VBA Copy Sheet to New/Existing Workbook
• VBA Check IF Sheet Exists
• VBA Activate Sheet (Worksheet.Activate)
• VBA Create New Sheet (Sheets.Add)