CODEWITHSUNDEEP
excelvba
Oren Webb
Oren Webb

Reputation: 3

Efficiency issue with Excel VBA loop

I need to go over a data with a few thousand lines and edit specific rows based on values of a few fields (if they meet some criteria).

The problem is that it takes ages (~13 seconds per loop, sometimes I have 100 loops)... I tried also to make VBA filter the table then work on filtered rows but it took the same time.

While l < CurrAloc And k <= lastrow
  If Cells(k, g) = "Pass" And Cells(k, h) <> "" And Cells(k, i) = "" And Cells(k, j) = "Available" Then

    Cells(k, ULDecCol) = CurrCustomer
    Cells(k, ULFromClassifierCol) = CurrClassifier
    add_to_log k
    Sheets("Unit List").Select
    l = l + 1
  End If

  k = k + 1
Wend

Upvotes: 0

Views: 49

Answers (1)

clinux
clinux

Reputation: 3074

Try

Application.Calculation = xlCalculationManual

before the while loop followed by

Application.Calculation = xlCalculationAutomatic

After the end of the while loop.

Upvotes: 1

Related Questions