Very Slow - Any alternatives

[kermitfrog123]

Hi All,

I have the below code which does exactly what I need. My problem is that is very slow to run. Can anyone offer any suggestions please?

Sub Sep_c_1_60()

    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        Worksheets("1-60% at C Status").Visible = True
        Worksheets("1-60% at C Status").Select
        Range("A3:DA1000").Delete Shift:=xlShiftUp
        
        Worksheets("Monthly Data").Visible = True
        Worksheets("Monthly Data").Select
        Dim lrSep As Long
        lrSep = Cells(Rows.Count, 1).End(xlUp).Row
        
        Dim dDateSep As Date
        Dim lDateSep As Long
        dDateSep = Worksheets(".lists").Range("B9")
        dDateSep = DateSerial(Year(dDateSep), Month(dDateSep), Day(dDateSep))
        lDateSep = dDateSep
        
        Dim ddelSep As Date
        Dim ldelSep As Long
        ddelSep = Worksheets(".lists").Range("B8")
        ddelSep = DateSerial(Year(ddelSep), Month(ddelSep), Day(ddelSep))
        ldelSep = ddelSep
  
                Range("A2:DA" & lrSep).AutoFilter Field:=4, Criteria1:=Worksheets("Dashboard").Range("U2").Value
                Range("A2:DA" & lrSep).AutoFilter Field:=6, Criteria1:="<=" & lDateSep
                Range("A2:DA" & lrSep).AutoFilter Field:=8, Criteria1:=">" & ldelSep, Operator:=xlOr, Criteria2:=""
                Range("A2:DA" & lrSep).AutoFilter Field:=61, Criteria1:=">0.00", Operator:=xlAnd, Criteria2:="<60.00"
        
        Range("A2:DA" & lrSep).Copy Sheets("1-60% at C Status").Range("A3")
                    
        Worksheets("Monthly Data").Select
        ActiveSheet.AutoFilterMode = False
        Range("A1").Select
                    
        Worksheets("1-60% at C Status").Select
        ActiveSheet.AutoFilterMode = False
        
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
End Sub

 

[Yongle]

Try resetting calculation to manual whilst macro is running should speed up both deletion and copy/paste operations

at beginning

        Application.Calculation = xlCalculationManual

at end

        Application.Calculation = xlCalculationAutomatic

 

[kermitfrog123]

This is much better, thank you.

Would there be anything else I could do?

 

[Yongle]

Copy\paste is slow. Attributing values is much quicker

so instead of this

[COLOR=#ff0000]Range("A2:DA" & lrSep)[/COLOR].Copy [COLOR=#000080]Sheets("1-60% at C Status").Range("A3")[/COLOR]

try something along these lines

Dim [COLOR=#ff0000]rng1[/COLOR] As Range, [COLOR=#000080]rng2[/COLOR] As Range
Set [COLOR=#ff0000]r[/COLOR][COLOR=#ff0000]ng1[/COLOR] = Range("A2:DA" & lrSep)
Set [COLOR=#000080]rng2[/COLOR] = Sheets("1-60% at C Status").Range("A3").Resize([COLOR=#ff0000]rng1[/COLOR].Rows.Count, [COLOR=#ff0000]rng1[/COLOR].Columns.Count)

[COLOR=#000080]rng2[/COLOR].Value = [COLOR=#000080]rng1[/COLOR].Value

You may need to format the new range separately

 

[Yongle]

I did not think that through properly
- I forgot that data is being filtered!

 

[kermitfrog123]

Thanks guys.

Implementing the Calculation off then On is much quicker. I have also noticed that If I leave calculation manual the code is extremely quick, it slows down when calculation is back to automatic. Is there a way to turn calculation to automatic but tell excel it doesnt need to calulate until the user next changes something??

 

[Yongle]

I am guessing you have thousands of formula in your worksheet(s)
Do you really need all of the formula in all rows - formula are only useful after the first time if underlying values are subsequently changed.
You could consider periodically "fixing" older data (by pasting values) and getting rid of your formula. Calculations would be much faster.
Would that be practical with your data? It only makes sense if a substantial proportion of the data does not change.

 

[Yongle]

tell excel it doesnt need to calulate until the user next changes something??

Yes that can be done using worksheet_change event. But do you really want the sheet to recalculate every time one value changes? It will recalculate for every change.

How is your workbook updated? Manual entry(one by one) Auto-updated (lots of rows at once)

Calculations only truly need updating just before results are needed. There are lots of triggers available - activating another sheet, clicking on a specific cell etc. Or a macro to recalculate at will. What would be convenient to the user?