Hello everybody,

I am trying to speed my macro a little bit, and I figured out that I can use array. But it's not working. Where is the issue?


Sheets("Source data LPD0" & LPD).Select
          
ArrayA = Range("A3:AT5000").Value

    For Lrow = 4997 To 3 Step -1

        PRange = 0.15 'If PV is higher or lower than SP by PRange*SP macro deletes the entry.

            
        For Lcol = 3 To 45 Step 3
                                   
                If ArrayA(Lrow, Lcol) = "#NA" Or ArrayA(Lrow, Lcol) = "NORecords" Then Range(ArrayA(Lrow, Lcol - 1), ArrayA(Lrow, Lcol + 1)) = ""
                If ArrayA(Lrow, Lcol) < 0.01 Or ArrayA(Lrow, Lcol) = "Null" Then Range(ArrayA(Lrow, Lcol - 1), ArrayA(Lrow, Lcol + 1)) = ""
                If ArrayA(Lrow, Lcol - 1) < 0.01 Or ArrayA(Lrow, Lcol - 1) = "Null" Then Range(ArrayA(Lrow, Lcol - 1), ArrayA(Lrow, Lcol + 1)) = ""
                If ArrayA(Lrow, Lcol + 1) < 5 Or ArrayA(Lrow, Lcol + 1) = "Null" Then Range(ArrayA(Lrow, Lcol - 1), ArrayA(Lrow, Lcol + 1)) = ""

                SP_MIN = ArrayA(Lrow, Lcol).Value * (1 - PRange)
                SP_MAX = ArrayA(Lrow, Lcol).Value * (1 + PRange)
                
                If ArrayA(Lrow, Lcol - 1) < SP_MIN Or ArrayA(Lrow, Lcol - 1) > SP_MAX Then Range(ArrayA(Lrow, Lcol - 1), ArrayA(Lrow, Lcol + 1)) = ""
                                         
        Next Lcol
    Next Lrow
    
Range("A3:AT5000").Value = ArrayA
The code without array is working OK, but very slow - 90 seconds:

Sheets("Source data LPD0" & LPD).Select
          
    For Lrow = 5000 To 3 Step -1
        
'---------------------------------------------------------------------------------------
        PRange = 0.15 'If PV is higher or lower than SP by PRange*SP macro deletes the entry.
'---------------------------------------------------------------------------------------
            
        For Lcol = 3 To 45 Step 3
                                   
            Set PV_Cell = Cells(Lrow, Lcol - 1)
            Set SP_Cell = Cells(Lrow, Lcol)
            Set CV_Cell = Cells(Lrow, Lcol + 1)
                        
                If SP_Cell = "#NA" Or SP_Cell = "NORecords" Then Range(PV_Cell, CV_Cell).ClearContents
                If SP_Cell < 0.01 Or SP_Cell = "Null" Then Range(PV_Cell, CV_Cell).ClearContents
                If PV_Cell < 0.01 Or PV_Cell = "Null" Then Range(PV_Cell, CV_Cell).ClearContents
                If CV_Cell < 5 Or CV_Cell = "Null" Then Range(PV_Cell, CV_Cell).ClearContents

                SP_MIN = SP_Cell * (1 - PRange)
                SP_MAX = SP_Cell * (1 + PRange)
                
                If PV_Cell < SP_MIN Or PV_Cell > SP_MAX Then Range(PV_Cell, CV_Cell).ClearContents
        
        Next Lcol
    Next Lrow
Thanks for any help!

Mario