CODEWITHSUNDEEP
vbaexcelms-office
Pedro Lastra
Pedro Lastra

Reputation: 63

EXCEL VBA How can i change an IF (loop) for something else?

I have this code that works but it takes a lot of time I´m sure there is a way to optimize it I did some research but i couldn't find how. My files are really big (100mb+) so anything that makes this code faster its necessary.

lastrowLaneTemplate = Sheets("LaneTemplate").Range("A65536").End(xlUp).Row
lastrowCarrier = Sheets("Routed").Range("B65536").End(xlUp).Row
lastrowCarrierd = Sheets("Routed").Range("B65536").End(xlUp).Row

 j = 2
  For i = 10 To lastrowLanetemplate
    For z = 2 To lastrowCarrier
     If Sheetlanetemplate.Cells(i, 4).Value <> "" Then
     If Sheetlanetemplate.Cells(i, 4) = sheetCarrier.Cells(z, 1) And _
     sheetCarrier.Cells(z, 3) = "1" Then

     sheetcarrierd.Cells(j, 1) = sheetCarrier.Cells(z, 1)
     sheetcarrierd.Cells(j, 2) = sheetCarrier.Cells(z, 2)
     sheetcarrierd.Cells(j, 3) = sheetCarrier.Cells(z, 3)
     sheetcarrierd.Cells(j, 4) = sheetCarrier.Cells(z, 4)
     sheetcarrierd.Cells(j, 5) = sheetCarrier.Cells(z, 5)
     sheetcarrierd.Cells(j, 6) = sheetCarrier.Cells(z, 6)
     sheetcarrierd.Cells(j, 7) = sheetCarrier.Cells(z, 7)
     sheetcarrierd.Cells(j, 8) = sheetCarrier.Cells(z, 8)
     sheetcarrierd.Cells(j, 9) = sheetCarrier.Cells(z, 9)
     sheetcarrierd.Cells(j, 10) = sheetCarrier.Cells(z, 10)
     sheetcarrierd.Cells(j, 11) = sheetCarrier.Cells(z, 11)

     j = j + 1
  End if
 Next z
Next y

As you can see that works but it takes some time and I have 10 times that.So if there is a way to do this without IF it would be perfect

Upvotes: 0

Views: 100

Answers (2)

Skjervesbu
Skjervesbu

Reputation: 36

Are you already using these?

At start:

Application.Calculation = xlCalculationManual

Application.ScreenUpdating = False

At the end:

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

Upvotes: 1

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

My quick idea is to change 11 lines inside if...end if statement into one line:

sheetcarrierd.Range(sheetcarrierd.Cells(j, 1), sheetcarrierd.Cells(j, 11)).Value = _
        sheetCarrier.Range(sheetCarrier.Cells(Z, 1), sheetCarrier.Cells(Z, 11)).Value

but I'm not sure if it would improve performance significantly.

Upvotes: 1

Related Questions