Return last row if there exists data in the sheet

Question

I am writing a function which will check if the already existing sheet is new or has data. If it consists data then it should return the last row, else it must return the first row. I am using the following code:

Private Function GetLastRow(sheetName As String) As Integer
Dim lastRow As Integer
lastRow = CurrentWorkbook.Sheets(sheetName).Cells.Find(What:="*", _
                After:=Range("A1"), _
                LookAt:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).End(xlUp).Row

GetLastRow = lastRow
End Function

But on debugging, I get an error that says that there is no object set. Is there any error in my code?

What is `CurrentWorkbook`, and what if a sheet contains more rows than an Integer can hold? Here is a reference to a more complete [function](https://codereview.stackexchange.com/a/95019/75176) — paul bica, Apr 05 '18 at 21:23

QHarr · Accepted Answer · 2018-04-05T21:33:04.740

1

Like this

Option Explicit

Public Sub TEST()

Debug.Print GetLastRow(ActiveSheet.Name)

End Sub

Private Function GetLastRow(ByVal sheetName As String) As Long

    Dim lastRow As Long

    With ActiveWorkbook.Sheets(sheetName)

        On Error GoTo returnVal

        lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
    End With

    GetLastRow = lastRow

    Exit Function

returnVal:

    GetLastRow = 1

End Function

edited Apr 05 '18 at 21:33

answered Apr 05 '18 at 21:23

QHarr

83,427
12
54
101

If you find the last populated row then use .End(xlUp), you'll likely end up on the first row. – Apr 05 '18 at 21:27
On debugging, xlUp = -4162, is it of any significance?? – Nimy Alex Apr 05 '18 at 21:29
@Jeeped Well spotted. – QHarr Apr 05 '18 at 21:31
@NimyAlex no, the -4162 is just the actual value of the `xlUp` variable defined by the [XlDirection Enumeration](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xldirection-enumeration-excel) – tigeravatar Apr 05 '18 at 21:32

score 1 · Answer 2 · answered Apr 05 '18 at 21:25

With these functions you can always find the last row and last column, you can feed sheet you are looking for the last row or last column and more importantly you can use which column or row is the criterion for you. If you omit the arguments, it will use the active sheet as the worksheet and it will use the first column to find the last row/cell and fist row to find the last column/cell

Function LastRowInColumn(Optional sh As Worksheet, Optional colNumber As Long = 1) As Long
    'Finds the last row in a particular column which has a value in it
    If sh Is Nothing Then
        Set sh = ActiveSheet
    End If
    LastRowInColumn = sh.Cells(sh.Rows.Count, colNumber).End(xlUp).row
End Function

Function LastColumnInRow(Optional sh As Worksheet, Optional rowNumber As Long = 1) As Long
    'Finds the last column in a particular row which has a value in it
    If sh Is Nothing Then
            Set sh = ActiveSheet
        End If
    LastColumnInRow = sh.Cells(rowNumber, sh.Columns.Count).End(xlToLeft).Column
End Function

score 1 · Answer 3 · answered Apr 05 '18 at 21:27

This is the one I use:

Public Function GetLastRow(ByVal arg_ws As Worksheet) As Long

    Dim rTest As Range

    Set rTest = arg_ws.Cells.Find("*", arg_ws.Range("A1"), xlFormulas, xlPart, , xlPrevious)
    If Not rTest Is Nothing Then GetLastRow = rTest.Row Else GetLastRow = 0

End Function

Call it like this:

Sub tst()

    MsgBox GetLastRow(ActiveWorkbook.Sheets("Sheet1"))

End Sub

score 0 · Answer 4 · edited Apr 05 '18 at 21:23

Reference the parent worksheet completely and use a long, not an integer. The end(xlup) was superfluous.

Private Function GetLastRow(sheetName As String) As Long
    Dim lastRow As long

    with ActiveWorkbook.workSheets(sheetName)
        lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              LookAt:=xlPart, _
                              LookIn:=xlFormulas, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
    end with
    GetLastRow = lastRow
End Function

Return last row if there exists data in the sheet

4 Answers4

Linked