Quickest way to clear all sheet contents VBA

Question

I have a large sheet that I need to delete all the contents of. When I try to simply clear it without VBA it goes into not responding mode. When using a macro such as:

Sub ClearContents ()  Application.Calculation = XlManual  Application.ScreenUpdating = False    Sheets("Zeroes").Cells.ClearContents   Application.ScreenUpdating = True End Sub 

It also doesn't respond. What's the quickest way to do this?

Answer 1

The .Cells range isn't limited to ones that are being used, so your code is clearing the content of 1,048,576 rows and 16,384 columns - 17,179,869,184 total cells. That's going to take a while. Just clear the UsedRange instead:

Sheets("Zeros").UsedRange.ClearContents 

Alternately, you can delete the sheet and re-add it:

Application.DisplayAlerts = False Sheets("Zeros").Delete Application.DisplayAlerts = True Dim sheet As Worksheet Set sheet = Sheets.Add sheet.Name = "Zeros" 

EDIT: (@tavnab and @Azura)
Heads up for future readers, you cannot delete a sheet if it's the last/only one in the workbook.
In that case, you can add the new blank sheet first, delete the old one, and finally rename that new sheet to the old sheet's name. Also note that eliminating a sheet will create conflicts with formulas in other sheets that were referencing the recently eliminated one, recreating the sheet may not solve that issue.