Speed Up Clear Contents

Question

Think my issue is trying to clear the 50,000 rows in multiple worksheets at the same time. Is there another way to do this? Possibly use a For loop in the range to speed up the process or a multirange array?

  Sub VBA_Clear_Contents_Range()
    Dim Dashboard_1, Dashboard_2, Dashboard_3, Data_1, Data_2, Data_3, Detail_1, Detail_2, 
    Detail_3 As Worksheet
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Set Dashboard_1 = Sheets("Dashboard_1")
    Set Dashboard_2 = Sheets("Dashboard_2")
    Set Dashboard_3 = Sheets("Dashboard_3")
    Set Data_1 = Sheets("Data_1")
    Set Data_2 = Sheets("Data_2")
    Set Data_3 = Sheets("Data_3")
    Set Detail_1 = Sheets("Detail_1")
    Set Detail_2 = Sheets("Detail_2")
    Set Detail_3 = Sheets("Detail_3")
           
    Dashboard_1.Range("AB11:AB15,AB21:AB25,AB28:AB32,AB38:AB42,L7:P7").ClearContents                
    Dashboard_2.Range("AB11:AB15,AB21:AB25,AB28:AB32,AB38:AB42,L7:P7").ClearContents
    Dashboard_3.Range("AB11:AB15,AB21:AB25,AB28:AB32,AB38:AB42,L7:P7").ClearContents
    
    Data_1.Range("A2:AG50000").ClearContents
    Data_2.Range("A2:AG50000").ClearContents
    Data_3.Range("A2:AG50000").ClearContents
    
    Detail_1.Range("A2:AG50000").ClearContents
    Detail_2.Range("A2:AG50000").ClearContents
    Detail_3.Range("A2:AG50000").ClearContents
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    End Sub


 

Answer 1

The first answer helps, along with that, you should look at combining your ranges, like this ...

Dashboard_1.Range("AB11:AB15,AB21:AB25,AB28:AB32,AB38:AB42,L7:P7").ClearContents

... it may not have a huge impact on performance but it will cut down the code and the amount of calls being made to each worksheet.