• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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

0
BrdgBldr
Asked:
BrdgBldr
  • 4
  • 2
  • 2
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now