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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-11
' @Description - Determining the last row containing data in 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.
' @Param tmpWS - an initialized Worksheet object.
' @Returns - the last row in given worksheet containing data as Long.
' If the given sheet in the given workbook does not conatin any data,
' this function returns -1.
Public Function getLastRowWithData(ByRef tmpWS As Worksheet) As Long
Dim lastRow As Long
On Error GoTo err_handler:
' Make sure all cells in handed worksheet are not merged.
If tmpWS.ListObjects.Count = 0 Then
Call tmpWS.Rows.UnMerge
Call tmpWS.Columns.UnMerge
End If
With tmpWS
If Application.WorksheetFunction.CountA(.Cells) > 0 Then
lastRow = .Cells.find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
Else
lastRow = -1
End If
End With
err_handler:
If Err.Number <> 0 Then
Err.Clear
End If
getLastRowWithData = lastRow
End Function
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
' @Author - Alexander Bolte
' @ChangeDate - 2014-10-11
' @Description - Returning the last row of a handed worksheet object relying on the UsedRange object.
' @Param ws - an initialized Worksheet object.
' @Returns - the last row of a provided Worksheet object.
' @Remarks - The UsedRange is pretty much unreliable.
' In order to make this work you will have to ensure that the workbook
' has been saved because only saving a workbook will actually refresh a UsedRange column and row indices.
' Even deleting rows or columns holding data before will be referenced by a worksheets UsedRange until corresponding workbook has been saved.
' Furthermore the UsedRange does not always start at the top left cell in a worksheet.
' If data only starts in cell B2, cell B2 will also be referenced as the start of the UsedRange within a worksheet.
Function getLastRow(ByRef ws As Worksheet) As Long
Dim ret As Long
On Error GoTo err_handle:
ret = ws.UsedRange.Rows.Count
err_handle:
If Err.Number <> 0 Then
End If
getLastRow = ret
End Function
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.
