CODEWITHSUNDEEP
excelvba
Nasos
Nasos

Reputation: 33

VBA excel code running time

I have written the below code to enter the current value of cells into a string array. There are 52k+ rows and when running in debug mode if I place a break on the 2nd "Do" the code runs really fast (seconds) after I press F5. If no break is entered then it takes a very long time (6-8 minutes).

Does anyone have any idea why this is happening and how it can be fixed?

Best, Nasos

Do
    ProjectIDPub(i, 0) = Cells(3 + i, 13).Value & " - " & Cells(3 + i, 3).Value
    ProjectIDPub(i, 1) = Cells(3 + i, 7).Value
    i = i + 1
Loop Until i = numberRowsAXProjectsSheetPub

i = 0
Do
    Cells(3 + i, 14).Value = ProjectIDPub(i, 0)
    Cells(3 + i, 15).Value = ProjectIDPub(i, 1)
    i = i + 1
Loop Until i = numberRowsAXProjectsSheetPub
i = 0

Upvotes: 0

Views: 213

Answers (2)

John Coleman
John Coleman

Reputation: 52008

Assuming that numberRowsAXProjectsSheetPub is the number of rows in the array ProjectIDPub, the entire second loop can be replaced by

Range(Cells(3,14),Cells(3 + numberRowsAXProjectsSheetPub - 1,15)).Value = ProjectIDPub

It is almost always much faster to transfer an array to the worksheet in a single assignment rather than in a loop.

Upvotes: 3

Pav
Pav

Reputation: 276

Have you tried running those loops with screen update disabled?

Application.ScreenUpdating = False
Do
ProjectIDPub(i, 0) = Cells(3 + i, 13).Value & " - " & Cells(3 + i, 3).Value
ProjectIDPub(i, 1) = Cells(3 + i, 7).Value
i = i + 1

Loop Until i = numberRowsAXProjectsSheetPub
i = 0
Do
    Cells(3 + i, 14).Value = ProjectIDPub(i, 0)
    Cells(3 + i, 15).Value = ProjectIDPub(i, 1)
    i = i + 1
Loop Until i = numberRowsAXProjectsSheetPub
i = 0
Application.ScreenUpdating = True

And as @ali-srn asked, why do you need to run the same loop twice? If the second loop access some data that needs to be created by the first one you should be able to alter it with some offset for i if needed and just run once.

Upvotes: 1

Related Questions