Efficiency issue with Excel VBA loop

Question

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

is it your entire code ? you have Sheets("Unit List").Select inside your loop, which is scanning thousands of lines as you say, and I am not sure what are you doing with this Select. Select takes a lot of time. — Shai Rado
– Shai Rado, Commented Jul 4, 2016 at 10:31
~13 secs is a lot. You shouldnt encounter performance problems with a single loop like that. Maybe you have a problem with your add_to_log? How long does it take if you dont write that log? If you use Selecting of a different sheet in you log: try to write to that sheet without selecting. — Jochen
– Jochen, Commented Jul 4, 2016 at 11:19

clinux · Accepted Answer · 2016-07-04 10:21:10Z

1

Try

Application.Calculation = xlCalculationManual

before the while loop followed by

Application.Calculation = xlCalculationAutomatic

After the end of the while loop.

answered Jul 4, 2016 at 10:21

clinux

3,1242 gold badges25 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oren Webb Over a year ago

It's so basic I'm honestly a little bit embarrassed :) Thanks!

Collectives™ on Stack Overflow

Efficiency issue with Excel VBA loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related