CODEWITHSUNDEEP
vbaexcel
Alenn G'Kar
Alenn G'Kar

Reputation: 123

Really slow For loop

How can I set my loop faster?

I have 4 columns and more than 250 rows and it takes like 5 minutes to loop this:

Application.Calculation = xlCalculationManual
Call CPU0

Dim TableA As Range:    Set TableA = Range("L_PriceA")

With TableA
LongA = TableA.Rows.Count
    For s = 1 To LongA
       .Cells(s, 3).Value = "dynamic"
       .Cells(s, 4).Value = "dynamic"
       .Cells(s, 5).Value = "dynamic"
       .Cells(s, 6).Value = "dynamic"
    Next
End With

"dynamic" will be data from xml because nobody helped me with THIS and I don't know :/. Also looked at some loop problems here in stackO, but no main boost.

Upvotes: 0

Views: 129

Answers (1)

chris neilsen
chris neilsen

Reputation: 53135

Looping over a range is slow. Avoid it by moving your data to Variant Array, something like this:

Dim dat As Variant
Dim s As Long

With TableA
    LongA = .Rows.Count
    dat = .Value  ' TableA must be at least 6 columns wide
    For s = 1 To LongA
       dat(s, 3) = "dynamic"
       dat(s, 4) = "dynamic"
       dat(s, 5) = "dynamic"
       dat(s, 6) = "dynamic"
    Next
    TableA.Value = dat
End With

Upvotes: 1

Related Questions