To clear an entire sheet using a VBA code in one go, you need to use two things, first is the CLEAR method, and then CELLS to refer to all the cells of a worksheet.
Clear the Activesheet
Let’s say if you want to clear all the content from the activate sheet, the code would be like below.
When you run this code, it will clear everything from all the cells from the active sheet, as I have already mentioned that we have used clear to refer to the entire worksheet and clear to clear it.
Clear a Specific Sheet
There’s one thing that you need to note down that to clear a sheet that sheet needs to be activated. So let’s say you want to clear the “Sheet1”, the code would be like:
In this code, the first activates the “Sheet1” and the second line clears everything.
Other Things to Clear
You can also use the below methods to clear different things.
Cells.ClearContents 'to clear contents Cells.ClearComments 'to clear Comments Cells.ClearFormats 'to clears formatting Cells.ClearHyperlinks 'to clear hyperlinks Cells.ClearNotes 'to clear notes Cells.ClearOutline 'to clears outline
Clear a Sheet from a Specific Workbook
The below code can refer to the workbook “Book1” and clear the sheet “Sheet1”. But make sure to have the workbook open at the time run this code.
This code first activates the “Sheet1” from the book and clear it.
Clear a Sheet from a Workbook which is Closed
And in the below code, we have referred to the “Sheet1” from the workbook “sample-file”, stored on a specific location.
Sub vba_clear_sheet() Dim wb As Workbook Application.ScreenUpdating = False Set wb = Workbooks.Open("C:\Users\Dell\Desktop\sample-file.xlsx") wb.Sheets("Sheet1").Activate Cells.Clear wb.Close SaveChanges:=True Application.ScreenUpdating = False End Sub
More Tutorials on Sheets
- VBA Worksheets – Understanding VBA Worksheet Object
- How to Add a New Sheet using VBA
- How to Activate a Sheet using VBA
- How to Copy a Sheet using VBA
- How to Rename a Sheet using VBA
- How to Hide a Sheet using VBA
- How to Delete a Sheet using VBA
- How to Count Sheets using VBA
- How to Protect a Sheet using VBA
- How to Check IF a Sheet Exists using VBA
- How to Loop Through all the Sheets in a Workbook