Forum Discussion

Mauricio Martínez's avatar
Mauricio Martínez
Copper Contributor
Oct 06, 2018
Solved

VBA // run-time error 1004: // Last Row

Hi.

When I am working with a Function in order to find the Last Row, the excel prompt a run-time error 1004:

 

The code:

Function LastRow() As Integer
   Dim endRow%
   endRow= Sheets(1).Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
   LastRow = endRow
End Function

 

Thanks.

Mauricio M.

Macros and VBA
run-time error 1004
  • Haytham Amairah's avatar
    Haytham Amairah
    Oct 07, 2018

    Hi Mauricio,

     

    Please try this instead:

    Function LastRow(Optional column_index_num) As Long
       On Error Resume Next
       
       If IsMissing(column_index_num) Then
            LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
       Else
            LastRow = Cells(Rows.Count, column_index_num).End(xlUp).Row
       End If
       
       On Error GoTo 0
    End Function

     

    You can use this function in the worksheet to get the last row number for the column in which the function was inserted and this is the default =LastRow().

    But you can pass a column index number to get the last row for a specific column like this: =LastRow(5)

     

    Hope that helps

3 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Oct 07, 2018

    Hi Mauricio,

     

    Please try this instead:

    Function LastRow(Optional column_index_num) As Long
       On Error Resume Next
       
       If IsMissing(column_index_num) Then
            LastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
       Else
            LastRow = Cells(Rows.Count, column_index_num).End(xlUp).Row
       End If
       
       On Error GoTo 0
    End Function

     

    You can use this function in the worksheet to get the last row number for the column in which the function was inserted and this is the default =LastRow().

    But you can pass a column index number to get the last row for a specific column like this: =LastRow(5)

     

    Hope that helps

Resources