Determining the last row actually containing information in an Excel worksheet can be an annoying task, if you are relying on the Excel API to do the trick for you. Especially when you are not exatcly in control over the handed files you are dealing with and people are allowed to enter information anywhere it is not as easy as it should be.
Source Code automating Excel search function
Below function is determining the last row containing data in provided worksheet using the Excel search function.
This approach works only, if no cells in given sheet are merged.
Therefore merged cells are removed from a handed workbook before determining its last row containing data.
Source Code using the UsedRange of a Worksheet
The below source code will only work for worksheets, which show at least one coulmn with content in the first row, because the used range does not start at the first row, but the first row showing content.
Furthermore the used range is only refreshed by Excel after a workbook has been saved. If cells are cleared or deleted and a workbook remains unsaved the used range is not refereshed and therefore remains as if the content is still available.
Below screens show the difference between used range with data in first row and without data in first row.
Used range does not work, if you intend to get the last cell with data in a worksheet.
And here it does work becaue I put content in the first cell.