VBA, for loop effiency

Question

all!

I would need to go through over 50k rows of data and delete rows with useless information. The current code works but is, unfortunately, all to slow... I tried understanding how it could be made more efficient but there wasn't easily found any concrete examples that I could have understood.

Here is the current code:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ThisWorkbook.Worksheets("FIC DATA WITH 3A").Activate
vika = Cells(Rows.Count, "B").End(xlUp).Row

r = 2
For i = 2 To vika                  
    If Not Cells(r, "J") > 42369 Then                       
        Rows(r).EntireRow.Delete
    ElseIf Cells(r, "I") = "OC" Or Cells(r, "B") = "Sales Doc." Or Cells(r, "B") = "" Then                     
        Rows(r).EntireRow.Delete
    Else
        r = r + 1
    End If
Next i

Could you maybe help? Concrete examples are more than appreciated.

how can it delete correctly when you are looping forward, you need to loop backward when deleting rows, it should be For i = vika To 2 Step -1 — Shai Rado
– Shai Rado, Commented Jun 13, 2017 at 8:01

YowE3K · Accepted Answer · 2017-06-13 08:14:41Z

2

In order to delete rows, you need to loop backwards For i = vika To 2 Step -1.

Also, there's no need to Activate "FIC DATA WITH 3A" sheet, in order to run the code on it.

The correct syntax to delete a row is Rows(i).Delete , and not Rows(r).EntireRow.Delete. If you want to use EntireRow, then the syntax is Range("A" & i).EntireRow.Delete (but doesn't make any sense to me why use it in this situation).

Code

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

With ThisWorkbook.Worksheets("FIC DATA WITH 3A")
    vika = .Cells(.Rows.Count, "B").End(xlUp).Row

    For i = vika To 2 Step -1
        If Not Cells(i, "J") > 42369 Or Cells(i, "I") = "OC" Or Cells(i, "B") = "Sales Doc." Or Cells(i, "B") = "" Then
            Rows(i).Delete
        End If
    Next i
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

edited Jun 13, 2017 at 8:14

YowE3K

24k7 gold badges28 silver badges45 bronze badges

answered Jun 13, 2017 at 8:08

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

1 Comment

Niklas Jalonen Over a year ago

Thank you, the reversing makes so much sense when you think about it!

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Shai Rado Answer about looping in reverse order is very important.

To speed up your code you need to convert Excel data into arrays rather than referring Cells in every loop iteration - it runs way faster.

Here is description how to achieve it: Arrays And Ranges In VBA

In your case you would need 2 arrays, one for I and J columns, second for B column.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 13, 2017 at 8:37

smartobelix

7485 silver badges16 bronze badges

1 Comment

smartobelix Over a year ago

You can take a look to another sample - I prepared loop optimization for different code, but rules remains the same - in this Answer link

Collectives™ on Stack Overflow

VBA, for loop effiency

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related