Quickest way to clear all sheet contents VBA

Solution 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.

Solution 2:

Technically, and from Comintern's accepted workaround, I believe you actually want to Delete all the Cells in the Sheet. Which removes Formatting (See footnote for exceptions), etc. as well as the Cells Contents. I.e. Sheets("Zeroes").Cells.Delete

Combined also with UsedRange, ScreenUpdating and Calculation skipping it should be nearly intantaneous:

Sub DeleteCells ()
    Application.Calculation = XlManual
    Application.ScreenUpdating = False
    Sheets("Zeroes").UsedRange.Delete
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
End Sub

Or if you prefer to respect the Calculation State Excel is currently in:

Sub DeleteCells ()
    Dim SaveCalcState
    SaveCalcState = Application.Calculation
    Application.Calculation = XlManual
    Application.ScreenUpdating = False
    Sheets("Zeroes").UsedRange.Delete
    Application.ScreenUpdating = True
    Application.Calculation = SaveCalcState
End Sub

Footnote: If formatting was applied for an Entire Column, then it is not deleted. This includes Font Colour, Fill Colour and Borders, the Format Category (like General, Date, Text, Etc.) and perhaps other properties too, but

Conditional formatting IS deleted, as is Entire Row formatting.

(Entire Column formatting is quite useful if you are importing raw data repeatedly to a sheet as it will conform to the Formats originally applied if a simple Paste-Values-Only type import is done.)

Solution 3:

You can use the .Clear method:

Sheets("Zeros").UsedRange.Clear

Using this you can remove the contents and the formatting of a cell or range without affecting the rest of the worksheet.