Excel VBA Clear Range

Sometimes, you may need to clear a range in Excel to remove unwanted data, such as erroneous entries and duplicates or unwanted formatting.

In this tutorial, I will show you several examples of clearing a range in Excel using VBA.

Note: It is essential to back up your data before using any of the provided example codes in this tutorial. The backup will allow you to restore your data should something go wrong. 

Example #1: VBA to Clear Contents and Formatting From the Active Worksheet

You can apply the ‘ActiveSheet.Cells.Clear’ method to remove the contents and formatting from the active worksheet. 

Suppose you have a large dataset filling up the entire active worksheet. You can use the code below to remove both the formatting and the contents from the sheet:

Sub ClearActiveSheet()
ActiveSheet.Cells.Clear
End Sub

When you run the code, it removes the contents and formatting from the active sheet, leaving it empty.

Also read: VBA to Remove Duplicates in Excel

Example #2: VBA to Clear Contents and Formatting From a Specific Range

You can use the ‘Range.Clear’ method to clear the contents and formatting from a range.

Suppose you have the dataset below on Sheet1 of the current workbook:

You can use the Excel VBA code below to clear the contents and formatting from the range:

Sub ClearRange()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:C7")
rng.Clear
End Sub

When you execute the code, it removes the content and formatting from the specified range, resulting in an empty range.

Example #3: VBA to Clear Formatting From Range and Preserve the Contents

You can utilize the ‘Range.ClearFormats’ method to remove the formatting from a range and retain the contents.

Suppose you have the dataset below on Sheet2 of the active workbook:

You can use the VBA code below to clear formatting from the range and retain the contents:

Sub ClearRangeFormats()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet2").Range("A1:C7")
rng.ClearFormats
End Sub

When you run the subroutine, it removes all formatting from the specified range while preserving the contents:

Also read: Delete Blank Rows Using VBA

Example #4: VBA to Clear Contents From Range and Preserve the Formatting

You can utilize the ‘Range.ClearContents’ method to remove the contents from a range and retain the formatting.

Suppose you have the dataset below on Sheet3 of the current workbook:

You can use the VBA code below to clear contents from the range and retain the formatting:

Sub ClearRangeContents()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet3").Range("A1:C7")
rng.ClearContents
End Sub

When you execute the code, it wipes out the content from the range while preserving the formatting:

Example #5: VBA to Clear Notes and Comments From Range

You can use threaded comments in Excel to engage with others in conversation regarding the data you are collaborating on. 

Note: Excel indicates a comment by a purple triangle in the top right corner of a cell. When you open the comment, it has a white background and a reply button or reply box.

You use notes in Excel to make annotations to data.

Note: Excel indicates a note by a red triangle in the top right corner of a cell. When you open the note, it has a yellow background.

If you no longer need the comments and notes in the data range, you can clear them using the ‘Range.ClearComments’ or ‘Range.ClearNotes’ method.

Assuming on Sheet4 of the active workbook,  you have the dataset below with comments and notes:

You can use the code below to clear the comments and notes from the range:

Sub ClearRangeCommentsNotes()
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("Sheet4").Range("A1:C5")
rng.ClearComments
End Sub

When you run the subroutine, it clears comments and notes from the specified range:

Example #6: VBA to Clear Hyperlinks From Range

Hyperlinks are links you can click to go to another location, such as a different cell in the same workbook, another workbook, a website, a file, or an email address. They help you access more information or resources related to the data. However, there may be times when you need to remove hyperlinks from a range.

Suppose you have the dataset below with hyperlinks on the active worksheet:

You can clear the hyperlinks from the data range using the code below:

Sub ClearRangeHyperlinks()
Range("A1:A2").ClearHyperlinks
End Sub

When you run the subroutine, it removes the hyperlinks from the range, and the hand icon no longer appears when you hover over the data.

Notice that the code does not clear the content or formatting of the data range, but it only makes the contents no longer clickable.

If you want to remove all hyperlinks from a worksheet, say the active sheet, you can use the code below:

Sub ClearActiveSheetHyperlinks()
ActiveSheet.Cells.ClearHyperlinks
End Sub

Also read: How to Remove Hyperlinks in Excel

Example #7: VBA to Clear the Contents and Formatting From the Used Range

The used range is the cell range in a worksheet that has been used or is currently in use.

You can use the code below to clear the used range in the active worksheet:

Sub ClearActiveSheetUsedRange()
ActiveSheet.UsedRange.Clear
End Sub

You can clear the used range in a specific worksheet by replacing the active sheet with that particular worksheet. For instance, the code below clears the used range in Sheet8 of the current workbook:

Sub ClearSpecificSheetUsedRange()
Worksheets("Sheet8").Cells.UsedRange.Clear
End Sub

Example #8: VBA to Clear the Contents and Formatting From the Current Region

In Excel, the current region refers to the block of cells surrounding a specified cell that contains data. Any combination of empty rows or columns encloses the region.

Suppose you have the dataset below on the active sheet: 

Notice cell B7  is the active cell.

You can use the code below to clear the current region:

Sub ClearActiveSheetCurrentRegion()
Dim rng As Range
Set rng = Range("B7")
rng.CurrentRegion.Clear
End Sub

When you run the subroutine, it clears cell C7 and all the cells with data surrounding it:

Also read: VBA to Copy Sheet to New Workbook

Example #9: VBA to Clear Outline From Range

You can use the Auto Outline or Group feature in Excel to create an outline for your dataset to organize and visually summarize it. You can use VBA to clear the outline if you no longer need it.

Suppose you have the following dataset on the active sheet with an outline applied to columns C, D, and E:

You can use the VBA code below to clear the outline from the range:

Sub ClearRangeOutline()
Range("C:E").ClearOutline
End Sub

When you run the subroutine, it removes the outline from the specified columns:

You can use the code below to remove outlines from the entire active worksheet:

Sub ClearActiveSheetOutline()
ActiveSheet.Cells.ClearOutline
End Sub

You can modify the code to clear outlines from a specific worksheet by replacing the active sheet with the specific worksheet. For instance, the code below clears outlines from Sheet7 of the current workbook:

Sub ClearSpecificSheetOutline()
Worksheets("Sheet7").Cells.ClearOutline
End Sub

Example #10: VBA to Clear Contents and Formatting  From the Current Workbook

You can use the code below to clear contents and formatting from all worksheets in the current workbook:

Sub ClearWorkbookAllSheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Cells.Clear
Next ws
End Sub

When you run the code, it loops through each worksheet in the active workbook, removing all formats and contents.

In this tutorial, I showed you several examples of clearing ranges in Excel using VBA. I hope you found the tutorial helpful.

Other Excel articles you may also like:

• What is VBA in Excel?
• How to Bold Text using VBA in Excel
• Select Range in Excel Using VBA