how can this loop be speeded up?

[jalea148]

iRowL is generally over 2000. Waiting for this loop to execute is like watching paint drying. Any suggestions will be appreciated.

Set Rng = Selection
For J = 2 To iRowL
X = Rng(J, 7) / Rng(J, 5)
For M = 2 To 4
Rng(J, M) = Rng(J, M) * X
Next M
Rng(J, 5) = Rng(J, 7)
Next J

 

[shg]

Drying agent?

Sub J148()
    Dim nRow        As Long
    Dim av          As Variant
    Dim i           As Long
    Dim j           As Long
    Dim d           As Double
    
    nRow = 22

    With Selection.Resize(nRow - 1, 7).Offset(1)
        av = .Value
        
        For i = 1 To nRow - 1
            d = av(i, 7) / av(i, 5)
            
            For j = 2 To 4
                av(i, j) = av(i, j) * d
            Next j
            
            av(i, 5) = av(i, 7)
        Next i

        For j = 2 To 5
            .Columns(j).Value = Application.Index(av, 0, j)
        Next j
    End With
End Sub

 

[jalea148]

Drying agent?

Sub J148()
    Dim nRow        As Long
    Dim av          As Variant
    Dim i           As Long
    Dim j           As Long
    Dim d           As Double
    
    nRow = 22

    With Selection.Resize(nRow - 1, 7).Offset(1)
        av = .Value
        
        For i = 1 To nRow - 1
            d = av(i, 7) / av(i, 5)
            
            For j = 2 To 4
                av(i, j) = av(i, j) * d
            Next j
            
            av(i, 5) = av(i, 7)
        Next i

        For j = 2 To 5
            .Columns(j).Value = Application.Index(av, 0, j)
        Next j
    End With
End Sub

WOW! That runs like greased lightning. Grazie mille [1000 thanx]

 

[shg]

You're welcome, glad it helped.

 

[jalea148]

Shg,
I'd like to understand why your solution is so much faster; it reduced the time from several minutes to under a second. If I understood, I'm sure I could profitably apply the method to many of my routines.