I have a code that loops through rows and evaluates values in a sheet and then adds 1 to the order if the condition is met. The problem is that the sheet is around 18,000 rows and the macro is running for three or four minutes... which is excessive.
The result should be like this:
CODIGO|MOTIVO|FAMILIA|ORDEN
1000 |9007 |1000 |1
350008| |1000 |2
1001 |9007 |1001 |1
35093 | |1001 |2
12534 | |1001 |3
1002 |9007 |1122 |1
35154 | |1122 |2
What I need to fill in is the order column, which starts from 1 every time I change families (also marked by reason field 9007).
This is the code that I have done and how long it takes me:
For i = 2 To uf
If Sheet1.Cells(i, 3).Value = 9007 Then
j = 1
Sheet1.Cells(i, 5).Value = j
Else
j = Sheet1.Cells(i - 1, 5).Value
Sheet1.Cells(i, 5).Value = j + 1
End If
Next
uf is the last row of the sheet.
Let's see if someone gives me an idea how to make it faster.
Thanks.
I have tested your code and it doesn't take that long, just about 10 seconds with 30 thousand records.
Perhaps the problem is that your book has many formulas, you could delay the calculation of the sheet with
Application.Calculation = xlCalculationManual
, that is put at the beginning of the macro, at the end it is putApplication.Calculation = xlCalculationAutomatic
In most cases it is faster to loop through data in memory, that is, convert a range to an array of data and loop through that array.
This example may help.
Well, using formulas it is done in just a second without complicating life much more:
So this is fine for me.