Quickest way to clear all sheet contents VBA

Quickest way to clear all sheet contents VBA

Asked on January 11, 2019 in Excel.
Add Comment


  • 3 Answer(s)

                To ones that may used, the .Cells range is not restricted this much code is clearing the content of 1,048,576 rows and 16,384 columns – 17,179,869,184 total cells. Alternatively, clear the UsedRange.

    Sheets("Zeros").UsedRange.ClearContents
    

    Another way to delete the sheet : 

    Application.DisplayAlerts = False
    Sheets("Zeros").Delete
    Application.DisplayAlerts = True
    Dim sheet As Worksheet
    Set sheet = Sheets.Add
    sheet.Name = "Zeros"
    
    Answered on January 11, 2019.
    Add Comment

    Try this alternative solution :

    • In case want to Delete all the Cells in the Sheet, use this syntax Sheets(“Zeroes”).Cells.Delete
    • In a PC it will clears 10000 lines and 2000 columns within a half a second.
    • If want to be nearly intantaneous,  alternatively joined with UsedRange and ScreenUpdating 
    Sub DeleteCells ()
        Application.Calculation = XlManual
        Application.ScreenUpdating = False
        Sheets("Zeroes").UsedRange.Delete
        Application.ScreenUpdating = True
    End Sub
    
    Answered on January 11, 2019.
    Add Comment

    This command will explain about quickest way to clear all sheet contents VBA

    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
    
    Answered on January 11, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.