SOLVED

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

Copper Contributor

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.

3 Replies
best response confirmed by Mauricio Martínez (Copper Contributor)
Solution

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

Hi Haytham.

Thanks for your feedback.

 

It worked perfectly !

 

Regards.

Mauricio M.

@Haytham Amairah 

 

Thank you very much. This helped me as well

 

Kind regards

 

Rene Linssen

Netherlands

1 best response

Accepted Solutions
best response confirmed by Mauricio Martínez (Copper Contributor)
Solution

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

View solution in original post