Excel has a UsedRange property that returns a list of all the cells ever touched. It isn't super accurate and can be easily fooled. The best way is to use that and then check to make sure that the last row is actually "used (not empty). If it is iterate backwards through the rows until you reach a row that has a value by calling GetCellValue.
Attached is the automation that returns the Used Range (thank you to JeffBadger for this). I have attached the automation, a screenshot showing how to select the input parameter of _Worksheet, and how to call it in your automation.