[SOLVED] Quickest way to clear all sheet contents VBA

Quickest way to clear all sheet contents VBA

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?

Solution

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.