0

I wrote down a macro which was use to run in less than 1 second. However, after an windows/Excel update the macro now takes 5 minutes to complete !

Could you please advice ?

Below my macro

Sub B_Palett_Weight_Check()        
    Dim ws As Worksheet
    Dim Vws As Worksheet

    Set Vws = ThisWorkbook.Sheets("Variables")

    Sheets("Sheet1").Select

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Application.PrintCommunication = False
    ActiveSheet.DisplayPageBreaks = False
    Application.DecimalSeparator = ","
    Dim target As Variant
    Dim r_AH As Range
    Dim n As Long
    Dim i As Long

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Variables" Then
            ' For the target value for each worksheet
            target = Application.WorksheetFunction.VLookup(ws.Name, Vws.Range("A1:C10"), 2, False)


            'Find the number of cells in column AH
            n = ws.Range(ws.Range("AH3"), ws.Range("AH3").End(xlDown)).Rows.Count
            Set r_AH = ws.Range("AH3")

            For i = 1 To n
            ' Go down the column AH
                If r_AH.Cells(i, 1).Value >= target Then
                    r_AH.Cells(i, 1).Interior.Color = vbRed
                Else
                    r_AH.Cells(i, 1).Interior.Color = vbWhite
                End If
            Next i
        End If
    Next ws
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
brice
  • 5
  • 1
  • Can you debug it and identify the line or lines of code that are particularly slow? Could it be that you're iterating over every row in the sheet, and you updated to a new version of excel with a lot more rows per sheet? – JeffUK Feb 25 '19 at 11:32
  • Why not just use conditional formatting? – John Coleman Feb 25 '19 at 11:35
  • If your `n` is small, there is nothing about this code which should take much time. Something else is going on. Just what, I have no idea. – John Coleman Feb 25 '19 at 11:45
  • @JeffUK No I use a file which got the same number of row ? I don't kwon how to assess code's line perfomance. However the macro ran faster before the update. – brice Feb 25 '19 at 11:47
  • Sometimes cells which have entries look blank, it's possible that xldown is finding the whole column, even if you only see 30 entries. – JeffUK Feb 25 '19 at 11:50
  • What does `Debug.Print n` tell you? – John Coleman Feb 25 '19 at 11:53

1 Answers1

1

I think the problem lies in n, because the way you calculate it, it goes to the last row of the sheet, which in newer versions is 1048576 instead of 65536. If you have updated from 2003 to higher, your for loop has become significantly larger.

I think you want to calculate n like this:

n = ws.Range(ws.Range("AH3"), ws.Range("AH3").End(xlUp)).Rows.Count

By using xlUp, n will be the last used row in column AH.

Alex de Jong
  • 1,257
  • 1
  • 11
  • 23
  • From OP's description, it seems that there is data below `"AH3"` -- in which case this is unlikely to be the issue. Still, it is a bug waiting to happen, so this answer could help. – John Coleman Feb 25 '19 at 14:01