asked on

VBA taking too long

The code is stuck on Range("K:K").Value = Range("K:K").Value
If the calculation is turned to automatic, it runs quickly.
But if calculation is on auto, it sits for hours.

Can anyone help?

Sub Count_Dim()

Application.ScreenUpdating = False
Sheets("Change box size").Activate
    Dim LastRow As LongPtr
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "J").End(xlUp).Row
    End With

With Range("K2")
.Formula = "=COUNTIFS(G:G,G2,B:B,B2)"
.AutoFill Destination:=Range(.Offset(0, -1), .Offset(0, -1).End(xlDown)).Offset(0, 1)
End With

    Range("K:K").Value = Range("K:K").Value
    
Range("K1") = "# Package Dimension"
Application.ScreenUpdating = True

Open in new window

Wayne Taylor (webtubbs)

If the calculation is turned to automatic, it runs quickly.

Do you mean if the calculation is set to Manual? How many rows of data is there?

If you replace that line with this, does it go any quicker?

    With Range("K:K")
        .Copy
        .PasteSpecial Paste:=xlPasteValues
    End With

Open in new window

Lorenda Christensen

What are you trying to do with that line of code? Copy/paste formula values?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

Wayne Taylor (webtubbs) - sorry, yes, I meant if it is on manual, it runs quickly. If on auto, it sits.
HOWEVER, If I turn on manual, do the pasting, then turn auto back on - the column NEVER CALCULATES.
Everything is the value of 1.

Trying solutions now.

Euro5

ASKER

Lorenda Christensen - yes copy the formula down, then save as a value.