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

User · Accepted Answer

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

User · Answer

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

User · Answer

Try this one   Sub clear sht   Dim sht As Worksheet   Set sht   Worksheets GENERATOR SHT NAME    col cnt   sht UsedRange Columns count   If col cnt   0 Then     col cnt   1   End If    sht Range sht Cells 1  1   sht Cells sht UsedRange Rows count  col cnt   Clear End Sub

User · Answer

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

[vba] Quickest way to clear all sheet contents VBA

Examples related to vba

Examples related to excel