We help IT Professionals succeed at work.

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

BrdgBldr
BrdgBldr asked
on
Medium Priority
297 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
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

Author

Commented:
thank you

Author

Commented:
Sid, with the "Exit for" it's not working...

Author

Commented:
thanks!
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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

:)
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

Author

Commented:
simultaneously! yeah, these last versions work perfectly! thanks to everybody!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.