Stopping loop in Range if there is no value in row

EM1987,
Please use code tags with your code as per forum's rule.
You can add

Exit for

below

Please Login or Register  to view this content.

Assuming that you have at least one column which always has data, you could use the following:

Please Login or Register  to view this content.

It will find the last row in your worksheet based on your column A and then add data to C.

You can use your existing code.

Please Login or Register  to view this content.

Thanks for this, but it stops after the first cell has been changed. Is there any way to base it on the other cells in the range i.e. Rows 1-4000, so if there is anything in those rows then the cell value would change to "ERROR"; but if the row is blank then it will skip those rows?

(Apologies about the code tags... didn't know about that rule but I'll make sure that I do it in the future!)

Formula:

Please Login or Register  to view this content.

I am not sue what you are asking.
The code loops from C2 until the last row in column C. So, if you rows in C do not go far, you are testing the wrong column.
Since you are testing column C, the code matches the lastrow with the last data in that column.
What if the code finds the first empty? If you want to exist the loop once an empty row in column C is found, do you want to exit the loop or? If so, add the exit for as I indicated in my first post.

Norie,

Is there any column that will always be fully populated?

IMO, If one is testing for column C, I thought the last row with the data should come from that column.

Unfortunately there is no guarantee that all cells in each column will be populated, but there will be data in each row and this is to pick up blank cells if there is some data in each row.

Thanks

There is no each column in your code.

Please Login or Register  to view this content.

This line loops from C2 to C4000, so You are ONLY looping through column C, no other column.

Norie,
Yes, I agree, as long as you are looking for the last row in a data (All columns), but the OP code is looping through column C

Norie,
I do not know!

I am only going by the loop in column C, so no need to check other columns for last data, if you are testing column C.

There is data in most of the columns (A-I), but the rows vary from week-to-week. So the loop is to pick up missing data in column c, and should be based on whether columns A,B,D,E etc have data in it

Okay, this will pick-up the last row in whole data.

Please Login or Register  to view this content.

Another option to replace blanks in column C

Please Login or Register  to view this content.

It worked, thanks for your help!!

Or

Please Login or Register  to view this content.

Norie,
Yes, your understanding was correct from outset, except, you wanted to evaluate all columns , while the OP code says column C.
I still think that using a column C as the last row evaluator works. If the last blank row in column C is in row 3 and the last row in the data is 1m, why does the code need to loop through all these rows while there are no data in column C after row 3?

Norie,
Both of your codes are right, but the first code should use the last row in entire data as you are evaluating all columns. So to make sure that you capture all data in all columns, you look for other columns with last row data.

My point was based on OP's code testing column C.
I still do not see why the code needs to find the last row in other columns while you are testing column C.
E.g may help.

Please Login or Register  to view this content.

I have data in column C in rows 2 and 3.
Now, If I were to use the LR, the code loops until the last row in the data, while LR1 stops at row 3, which is the point.
Why the code needs to loop through the longest row, while there are no data in column after row 3 to test.
The code should stop looping at the last row in column C, which is row 3.
I hope you get my point.

Norie,
I was referring to post #6

"Is there any column that will always be fully populated?"

Norie,
The question origin is

Please Login or Register  to view this content.

Why are you brining other columns in the equation while you are testing column C?

Are you implying this line will not find the last "REAL RAW DATA" in column C?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Will return the last row in the active sheet.

Return =10

Please Login or Register  to view this content.

Return=7

I do not need to loop through 10 rows, while the column I am testing last row is 7.

Norie,
Okay!
Good point!
But my reading was based on the first line of the code.

But why do I need to fill in(Test it) rows 8 and beyond, while my last row for which I am testing a column says 7?

to find the last row of data of which column? I am not interested in columns A-B and D. I am testing

Please Login or Register  to view this content.

which clearly says column C, not

Please Login or Register  to view this content.

Yes , if I am using the second line, but the OP words and the first line of the code are not the same.