VBA Macros running slow out of nowhere

[Jeff2554]

Hello all,
I'm hoping someone can take a peak at my file and see if they can figure out why the macros are running so slow.
When I originally built the workbook, the macros would execute in less than a second. Out of nowhere, they slowed way down and are prone to crash.
The first time this happened, I went to an older save and rebuilt the whole file which then fixed the issue and I had two separate saves that worked great.
Much to my dismay when I came back to work on Monday, they both run super slow and/or crash with no errors.
The code is not super complex, and when I step through the macros it seems to get stuck on operations like inserting cells, copying, and pasting.
I also have each sheet locked into only one selection to prevent things from being selected and potentially having formulas get broken.
Hence the application.EnableEvents at the beginning and end of the macros.

Sub SendIt()

Dim rng As Range
Dim cell As Range
Dim search As String
Dim lane As String
Dim dist As String

Application.EnableEvents = False

If ThisWorkbook.Sheets("Main Sheet").Range("B11") = "Enter Part" Then
    MsgBox "Please enter a valid part number, thanks."
    ThisWorkbook.Sheets("Main Sheet").Range("B6").Select
    Selection.ClearContents
    Application.EnableEvents = True
    Exit Sub
End If

On Error Resume Next
    ThisWorkbook.Sheets("Log").ShowAllData
On Error GoTo 0

'Log Information
Call Log_Improved

Set rng = ThisWorkbook.Sheets("Lanes").Columns("D:D")
search = ThisWorkbook.Sheets("Main Sheet").Range("B6")
Set cell = rng.Find(What:=search, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

Worksheets("Lanes").Activate
cell.Select
lane = ActiveCell.Offset(0, -2)
cell.Select
dist = ActiveCell.Offset(0, -1)


'Set Range of Lane the part goes to

Worksheets("Lanes").Activate
cell.Select
ActiveCell.Offset(0, dist) = ActiveCell.Offset(o, dist) + 1

Worksheets("Main Sheet").Activate
ThisWorkbook.Sheets("Main Sheet").Range("B6").ClearContents

Application.EnableEvents = True

End Sub

Sub Log_Improved()

    Dim P As Range
    Dim L As Range
    Dim T As Range
    Dim D As Range
    
    Set P = ThisWorkbook.Sheets("Log").Range("L3")
    Set L = ThisWorkbook.Sheets("Log").Range("L4")
    Set T = ThisWorkbook.Sheets("Log").Range("L5")
    Set D = ThisWorkbook.Sheets("Log").Range("L6")
    
    ThisWorkbook.Sheets("Log").Range("E8:H8").Insert Shift:=xlDown
    ThisWorkbook.Sheets("Log").Range("M3:M6").Insert Shift:=xlToRight

    ThisWorkbook.Sheets("Log").Range("E8") = P
    ThisWorkbook.Sheets("Log").Range("F8") = L
    ThisWorkbook.Sheets("Log").Range("G8") = T
    ThisWorkbook.Sheets("Log").Range("H8") = D

    ThisWorkbook.Sheets("Log").Range("M3") = P
    ThisWorkbook.Sheets("Log").Range("M4") = L
    ThisWorkbook.Sheets("Log").Range("M5") = T
    ThisWorkbook.Sheets("Log").Range("M6") = D
    
    ThisWorkbook.Sheets("Main Sheet").Range("H2:H5").Insert Shift:=xlToRight
    
    ThisWorkbook.Sheets("Main Sheet").Range("H2") = P
    ThisWorkbook.Sheets("Main Sheet").Range("H3") = L
    ThisWorkbook.Sheets("Main Sheet").Range("H4") = T
    ThisWorkbook.Sheets("Main Sheet").Range("H5") = D
    
End Sub

Sub Clear()
'
' Clear Macro
'
Application.EnableEvents = False
'Move Stock
    Range("NHKStock").Copy ThisWorkbook.Sheets("Lanes").Range("AI4")

'Move Leftmost Lane
    Range("NHKLeftmost").Copy ThisWorkbook.Sheets("Lanes").Range("AF3")

'Reformat
    Range("AllNHKLanes").Copy ThisWorkbook.Sheets("Lanes").Range("E3")

'Change next load number
    ThisWorkbook.Sheets("Lanes").Range("AC4") = ThisWorkbook.Sheets("Lanes").Range("Z4") + 1
    
'Clear Current from moved lane
    ThisWorkbook.Sheets("Lanes").Range("AD7:AD37").ClearContents
    
'Clear Load from Pullsheet
    ThisWorkbook.Sheets("Pullsheet Drop").Range("D1:D34").Delete
    
Application.EnableEvents = True
    
End Sub

 

[rlv01]

it seems to get stuck on operations like inserting cells, copying, and pasting.

Sometimes when a worksheet gets very large, those kinds of operations slow down.

Also if there is a corporate IT department in the mix pushing updates to your PC, what they do can sometimes have a big effect. If the real-time AV scanner becomes interested in what Excel is doing, that can slow things down- something I have experienced myself. You can usually tell if something like this is going on by launching Task Manager, then sorting the process window by CPU usage.

 

[mumps]

If you have a large number of formulae in you workbook, they can slow things down if they are constantly re-calculating. Your code can be revised to make it more efficient. I think that it would be easier to help and test possible solutions if we could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[mumps]

I just noticed that you posted a similar question at this link: Macro runs very slow
Is your problem now solved?

 

[Tanquen]

I ran into this today. I have a Workbook that I've been working on for months. Today seemly out of nowhere VBA scripts I've used many times, just the other day, are very slow.

I have a lot of scripts linked to buttons on screen and all was well until today.

I have started disabling screen and calculation updates as some of the VBA can take a long time to run. These help a lot but had not been needed on most of the VBA scripts until now.

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

Added these on a different sheet last night and if I remove them the VBA on another sheet run faster and is back to normal.

=SUMPRODUCT((SUBTOTAL(103, OFFSET(K3:K1048576, ROW(K3:K1048576)-MIN(ROW(K3:K1048576)), 0, 1)))*(K3:K1048576 > O5))
=SUMPRODUCT((K3:K1048576 > O5) * (SUBTOTAL(103, OFFSET(K3, ROW(K3:K1048576)-ROW(K3), 0))))
=SUMPRODUCT((SUBTOTAL(103, OFFSET(K3:K1048576, ROW(K3:K1048576)-ROW(K3), 0, 1)))*(K3:K1048576 > O5))

I'm just trying to get a simple formula to not include hidden rows. It seems there is no easy way to do this.

=COUNTIF(K3:K1048576, ">" & O5)

Is there a way to see what formulas are making the most impact and slowing everything else down. I don't feel like I have that many.

I forgot, there is the new check performance, that can help for some issues.