Excel VBA change loop from row 1 to n the other way around to n to 1

This loop searches for a specific date in a range. as the range is a few thousand rows large and the searched date is always much closer to the last/highest non-empty row than row 1, i am looking for a solution to reverse this code in order to speed it up:


For i = 1 To LastRowWeeklySource
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then PrxdWeeklySourceRow = i
Next

Open in new window

BrdgBldrAsked:
Who is Participating?
 
Rory ArchibaldCommented:
For i = LastRowWeeklySource to 1 step -1
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then PrxdWeeklySourceRow = i
Next i

Open in new window

0
 
SiddharthRoutCommented:
NFP

For i = LastRowWeeklySource to 1 step -1
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then PrxdWeeklySourceRow = i
    Exit For
Next i

Open in new window


Sid
0
 
BrdgBldrAuthor Commented:
thank you
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
BrdgBldrAuthor Commented:
Sid, with the "Exit for" it's not working...
0
 
BrdgBldrAuthor Commented:
thanks!
0
 
Rory ArchibaldCommented:
It should be:
For i = LastRowWeeklySource to 1 step -1
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then 
      PrxdWeeklySourceRow = i
      Exit For
   End If
Next i

Open in new window

:)
0
 
SiddharthRoutCommented:
Sorry the code should have been

For i = LastRowWeeklySource To 1 Step -1
    If WeeklyWs.Range("A" & i).Value = LastProcessedWeekly Then
        PrxdWeeklySourceRow = i
        Exit For
    End If
Next i

Open in new window


Sid
0
 
BrdgBldrAuthor Commented:
simultaneously! yeah, these last versions work perfectly! thanks to everybody!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.