Very Slow - Any alternatives

kermitfrog123

Board Regular
Joined
Dec 19, 2015
Messages
50
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?

Code:
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
Try resetting calculation to manual whilst macro is running should speed up both deletion and copy/paste operations

at beginning
Code:
        Application.Calculation = xlCalculationManual
at end
Code:
        Application.Calculation = xlCalculationAutomatic
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
Copy\paste is slow. Attributing values is much quicker

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

try something along these lines
Code:
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
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

kermitfrog123

Board Regular
Joined
Dec 19, 2015
Messages
50
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,746
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,020
Messages
5,526,296
Members
409,694
Latest member
bastos21

This Week's Hot Topics

Top