End(xlDown) error if only 1 row

[mdr8c]

Hello!

I have the following code in my macro:
EndRow = Range("A16").End(xlDown).Row

I am using that code to identify the last row with data in it. For example, if I have numbers entered in cells A16, A17, and A18, the code finds the last row as row 18. However, the error occurs if I only have a number in cell A16. Even with no numbers in the subsequent rows, the EndRow command jumps to the last line of the entire spreadsheet (65536). How can I change the code so that if I only have 1 row of data, the EndRow stops at A16?

Thanks,
Margaret

 

[VoG]

Try

EndRow = Range("A" & Rows.Count).End(xlUp).Row

 

[mdr8c]

Thanks! That worked great. Now can you please explain to me why it worked?
I presume that "Rows.Count" counts how many rows have numbers in them, but then how does End(xlUp) make it find the bottom row?

 

[VoG]

It basically finds ("A" & Rows.Count) which is A65536 in Excel prior to XL 2007, then looks back up to the last filled row.

 

[mdr8c]

Got it. Thanks for your help!