xlUp not working in For loop

[Chr15]

I have been tasked with debugging some code at work and have found an easy fix for it, but don't understand why it works..

The original code:
For x = 6 to Cells(10000, 1).End(xlUp).Row

The "repaired" code:
last_row = Cells(10000, 1).End(xlUp).Row
For x = 6 to last_row

It was originally not running the For loop, as if it made Cells(10000, 1).End(xlUp).Row = 0.
But now it works. I just want to know if there is a reason for this or if it's just a coincidence.
Thank you for your help.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

I'm not spotting any difference between the expected result of those two versions.

If that's correct, it means you haven't really fixed the problem but instead are running the code under different conditions.

Possible problems with the code are..
1. If the last row is less than 6, the For statement will not execute any instructions.

2. Cells(10000,1) is not a fully qualified reference. If the Active Sheet is not the one you intended, the wrong row number will be returned.

This would be more reliable...

Sheets("Sheet1").Cells(Rows.Count,1).End(xlUp).Row

Good luck!

 

[Chr15]

Thanks.
The correct workbook and sheet are activated and there are over 2000 rows of data. It is the same data that wasn't working before.

The code you gave also works and I like it better, so I'm using it. These three bits of code all do the same thing so I don't see the difference either. Could it be that the
variable being assigned before the loop ensures that the calculation is complete before the loop is started?

I just thought that maybe there was some sort of Excel VBA issue with this sort of code that I didn't know about, you know a sort of preference that the language has. But if that's not the case then I don't know what it could be.
Thank you for your help

 

[Jerry Sullivan]

These three bits of code all do the same thing so I don't see the difference either. Could it be that the variable being assigned before the loop ensures that the calculation is complete before the loop is started?

There are a few processes in Excel VBA that can be run multi-threaded, but the statements in your example will be executed sequentially. So good thinking in seeing that as a possible explanation, but that isn't the cause of the different results.

Well that will remain a mystery for now. I can't count all the times I'd swear the same code gave me different results only to later figure out what was different about the environment/ conditions that caused that.

If it happens again, try to save a copy of that workbook and note the conditions when it occurred (active sheet, other workbooks open, etc).