VBA Clear Entire Sheet in Excel

If you are looking for VBA to clear the entire sheet in Excel then look no more, read on to find out what VBA to use to clear the entire sheet and start with a blank canvas…

 


 

VBA Clear Entire Sheet

With VBA it is possible to clear the entire sheet in Excel so that you are now working with a blank canvas.  This has many practical applications in the business world but one of the main reasons clearing the entire sheet is useful is when you no longer need large, memory hogging datasets.  For example you might have some VBA script that takes the raw data, builds summary tables or a dashboard and the raw data is no longer needed so can be cleared from the file.

VBA Script for clearing the entire sheet

The VBA for clearing the entire sheet is nice and straight-forward.  It can be achieved a few ways, like most things in Excel, but the common practice is to do the following:

Sheets(“INSERT WORKSHEET NAME HERE”).Cells.ClearContents

In practice this will perform the same operation as the clear contents option, so a raw data sheet that looks like this:

Can be cleared by inserting a module or macro with the script:

Sub ClearSheet()

Sheets(“Sheet1″).Cells.ClearContents

End Sub

Other useful clearing tips

The clear contents method is useful but sometimes there will be additional formatting on a worksheet, table borders, cell shadings etc.  Clear contents does not remove these formats, it simply clears the entire sheet of data, not formats.

To clear your entire sheet of data and formats you need to use a delete option and to do this we simply change our VBA script to:

Sub ClearSheet()

Sheets(“Sheet1″).Cells.Delete

End Sub

And Finally…

There may be times when deleting the entire sheet is the best option rather than just clearing the entire contents.  To do this we can again change our existing code to the following:

Sub ClearSheet()

Sheets(“Sheet1″).Delete

End Sub

Notice all we have done is removed the option for “cells” so that the “delete” command applies to the worksheet rather than the worksheet cells.  This applies for other VBA commands so is useful to remember.

Summary

VBA to clear an entire sheet in Excel uses the clear contents method, however all that does is clear the data from the cells and not the formatting.  To truly clear all the cells and start with a blank canvas you need to use the delete method.  Finally we looked at the structure of the VBA script and how to amend it to change your VBA from deleting the contents of cells to deleting the entire worksheet from your Excel workbook.

Keep Excelling,

Share

Leave a Reply

You must be logged in to post a comment.