Clear Sheet Using VBA

In this post you will learn how to clear a sheet using VBA. Excel sheets can contain various elements and objects. So I will show you how you can clear these various items using VBA. You will learn how to clear a specific range or an entire sheet. Also you will learn how to clear things from an active sheet or a specific sheet. Inbuilt clear methods don't allow us to delete shapes. So at the end I will show how to create our own VBA code to delete objects such as charts, shapes etc. To explain about these various methods I will use this sample Excel sheet.

This Excel sheet consists of text, values, hyperlink, chart and a star. Also some formattings are applied to the sheet such as colors and borders.

There are various ways to clear Excel sheets. But not all the methods can be used to clear all the things from the sheet. So let’s see how we can use these different methods and their outcomes.

Here is the first method you can use to clear an Excel sheet using VBA.

Sub ClearSheet_Example1()

     Cells.Clear

End Sub

In this method we haven’t specified a sheet. So the clear method will be applied to the active sheet. If you run the above VBA code, contents, formats, comments and hyperlinks will be deleted from the active sheet. But the shapes and charts will not be deleted. If the source data of the chart is in the same sheet, then the chart will be blanked. This is what happened to our sample Excel sheet after running above code. (In our sample excel sheet, source data of the chart was in the same sheet.)

Next let’s look at how to modify the above VBA code to clear a specific sheet from a workbook. Assume we have a workbook with multiple worksheets and we need to clear the sheet called “Order Details”. You can modify the above subroutine to clear the “Order Details” sheet as follows.

Sub ClearSheet_Example2a()

     Worksheets("Order Details").Cells.Clear

End Sub

If you want, you can declare a variable of type worksheet and then assign this worksheet to the variable and clear it as well.

Sub ClearSheet_Example2b()

     Dim WS As Worksheet

     Set WS = Worksheets("Order Details")

     WS.Cells.Clear

End Sub

Also the Clear method can be used to clear a specific range of a worksheet. Following subroutine will clear the range A6:B10 of the activesheet.

Sub ClearSheet_Example3()

     Range("A6:B10").Clear

End Sub

This is what happened to our sample worksheet after running the above macro.

In the above examples we cleared everything except shapes and charts. However if you want you can clear only particular things from an Excel sheet or a range. Here is the list of things you can clear using VBA, without clearing everything.

• Comments
• Contents
• Formats
• Hyperlinks
• Notes
• Outlines

Now let’s look at how to clear each of these things separately.

Following subroutine will clear all the comments of the active sheet.

Sub ClearComments()

     Cells.ClearComments

End Sub

You can use the below macro to clear the contents from range A8:B12.

Sub ClearContents()

     Range("A8:B12").ClearContents

End Sub

Only contents are removed from that area keeping the table formats.

Also we can clear only formats from the whole sheet or specific range of a sheet. This next macro will clear all the formats from the table (range A1:B10) of the “Data” sheet.

Sub ClearFormats()

     Worksheets("Data").Range("A1:B10").ClearFormats

End Sub

Here is the result of the above subroutine.

Sometimes you may want to remove the hyperlinks of the entire sheet or from a range of cells using VBA. For that you can use the ClearHyperlinks method as follows.

Sub ClearHyperlinks()

     Cells.ClearHyperlinks

End Sub

Above macro will remove all the hyperlinks of the activesheet. However note that the formattings and the text of the hyperlinks will not be removed. Only the link will be removed.

Here is how to clear all the notes of the Excel sheet using VBA.

Sub ClearNotes()

     Cells.ClearNotes

End Sub

Next let’s look at how to clear the outline from an Excel sheet. Assume we have an outline like this in our worksheet.

Here we have grouped rows from 2 to 10 using an outline. We can remove this outline automatically using the ClearOutline method as follows. Name of the worksheet is “Data”.

Sub ClearOutline()

     Worksheets("Data").Cells.ClearOutline

End Sub

So far we learnt various ways to clear sheets. But none of them were able to delete the objects such as shapes, charts etc. Inbuilt clear methods don’t clear these objects from the sheet. We need to create our own code to delete these items. Next let’s look at how to delete these objects from a worksheet automatically using VBA. To do this we need to iterate through each and every shape using a For Each loop and delete them. Here is the code to delete all the shapes from the active sheet.

Sub DeleteAllObjects()

     Dim Sh As Shape

     For Each Sh In ActiveSheet.Shapes
         Sh.Delete
     Next

End Sub

If we run this subroutine in our sample Excel sheet, the result will look like this.

Check this post if you want to learn more about manipulating shapes using VBA.

Shapes

We can also add the Clear method to this subroutine. Then everything will be cleared from the sheet.

Sub ClearEverything()

     Dim Sh As Shape

     For Each Sh In ActiveSheet.Shapes
         Sh.Delete
     Next

     Cells.Clear

End Sub

Above macro will clear everything in the activesheet. You can also modify the above VBA code to clear everything from a specific sheet as well. Assume we want to clear everything from a sheet called “Data”. For that we can easily modify the above subroutine as follows.

Sub ClearEverything_SpecificSheet()

     Dim WS As Variant
     Dim Sh As Shape

     Set WS = Worksheets("Data")

     For Each Sh In WS.Shapes
         Sh.Delete
     Next

     WS.Cells.Clear

End Sub