SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-267849%22%20slang%3D%22en-US%22%3EVBA%20%2F%2F%20run-time%20error%201004%3A%20%2F%2F%20Last%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267849%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%3C%2FP%3E%3CP%3EWhen%20I%20am%20working%20with%20a%20Function%20in%20order%20to%20find%20the%20Last%20Row%2C%20the%20excel%20prompt%20a%20run-time%20error%201004%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20code%3A%3C%2FP%3E%3CP%3EFunction%20LastRow()%20As%20Integer%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BDim%20endRow%25%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BendRow%3D%20Sheets(1).Cells(Rows.Count%2C%202).End(xlUp).Offset(1%2C%200).Row%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3BLastRow%20%3D%26nbsp%3BendRow%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3EMauricio%20M.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-267849%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Erun-time%20error%201004%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267961%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20%2F%2F%20run-time%20error%201004%3A%20%2F%2F%20Last%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267961%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3EHaytham%3C%2FA%3E.%3C%2FP%3E%3CP%3EThanks%20for%20your%20feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20perfectly%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EMauricio%20M.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-267947%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20%2F%2F%20run-time%20error%201004%3A%20%2F%2F%20Last%20Row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-267947%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Mauricio%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20instead%3A%3C%2FP%3E%3CPRE%3EFunction%20LastRow(Optional%20column_index_num)%20As%20Long%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20On%20Error%20Resume%20Next%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20If%20IsMissing(column_index_num)%20Then%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20LastRow%20%3D%20Cells(Rows.Count%2C%20ActiveCell.Column).End(xlUp).Row%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20Else%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20LastRow%20%3D%20Cells(Rows.Count%2C%20column_index_num).End(xlUp).Row%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%20On%20Error%20GoTo%200%3CBR%20%2F%3EEnd%20Function%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EYou%20can%20use%20this%20function%20in%20the%20worksheet%20to%20get%20the%20last%20row%20number%20for%20the%20column%20in%20which%20the%20function%20was%20inserted%20and%20this%20is%20the%20default%20%3CSTRONG%3E%3DLastRow()%3C%2FSTRONG%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EBut%20you%20can%20pass%20a%20column%20index%20number%20to%20get%20the%20last%20row%20for%20a%20specific%20column%20like%20this%3A%20%3CSTRONG%3E%3DLastRow(5)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EHope%20that%20helps%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Mauricio Martínez
New 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.

2 Replies
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.

Related Conversations
runtime error 1004 : Method range of object - 'Global' failed
Lorenzo Kim in Excel on
16 Replies
sum by color when colors are set by conditional formatting
matt nipper in Excel on
197 Replies
Compile error: Method or data member not found
kevin whiteside in Excel on
5 Replies
Re: Macro to save as PDF with auto filename as cell value
Joe Gray in Excel on
23 Replies
Debug Error on Macro
Greg Bonaparte in Excel on
29 Replies