How To Find the Last Row That Contain Data in Excel?

Posted on the June 17th, 2008 under Excel VBA Function by Poer @ Excel VBA/Macro

UPDATE June 13, 2008:

Another alternative way to find the last row with data :

Function LastRow() As Long

    Dim ix As Long
    ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
    LastRow = ix

End Function

I did a simple test, and the function will return the correct last row position even when the data was filtered

Read also the opposite version of this function, instead of row, in here we find the last column containing data.

UPDATE Oct 25, 2008:

As you can see there are several way to find the last row that contain data in Excel, which you can found in the post comments below, please feel free to read it.

Today, when I trying to solve another problem of mine, I found more simple approach to find the last raw.

As you probably know, Excel has a special cell like blank cell, cell containing formula, cell with comments etc, let me quote it from Excel help.

xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

So, I think why not we use Excel special cell type xlCellTypeLastCell to find the row position of that LastCell? Isn’t the row position of last cell in the used range will be the same with the last row that contain data in Excel?

I then create a dummy sheet, fill it with data, and run this simple vba code, and yes, the vba code return the right position of last row with data in corresponding worksheet.

'
Cells.SpecialCells(xlCellTypeLastCell).Row
'

But unfortunately, again, this vba code won’t work on filtered Excel Worksheet, the code will return position of the last row which is not filtered.

Please try it, and leave me a comment.

ORIGINAL:

This one is my fav, I commonly used this simple Excel VBA function to get the last row on active worksheet that contain any kind of data.

The function will return the position of the last row, so we can assume that after that row, there are no other data on the active worksheet.

Public Function GetLastRowWithData() As Long

    Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long

    Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
    lLastDataRow = ExcelLastCell.Row
    lRow = ExcelLastCell.Row
    '
    Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1
        lRow = lRow - 1
    Loop
    '
    lLastDataRow = lRow

    GetLastRowWithData = lLastDataRow

End Function

We can call this formula from the cell formula bar and return the position of the last row with data, or we can also call it from others function or procedure (excel macro).

The most common use of this formula is when I need to paste some data to the next row after the last row that contain any data on my Excel sheet, and this formula really help to find the right location to paste it.

FIN.

Related Entries

External Resources

Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: excel vba find last row, excel vba formula, Excel VBA Function, last row with data

17 Responses to 'How To Find the Last Row That Contain Data in Excel?'

RSS

June 27, 2008 at 9:59 pm
JP
Doesn’t this produce the same answer, without the need for a loop?
```
Dim myLastRow As Long
myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

Then you could use it like this:

Function LastRow() As Long
LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
End Function

And in the worksheet:
=LASTROW()
```
FYI the “greater than” and “less than” signs are being changed to HTML tags in your code block. You might also want to consider deleting the “Hello World” post and the Mr. Wordpress comment.
–JP
July 7, 2008 at 8:49 am
How To Find the Last Row That Contain Data in Excel? - Learn Excel

[...] Original post by admin [...]
July 21, 2008 at 12:19 pm
Seth

JP,

That seemed to work for me but if I add more rows it does not update. Is there a solution to that. I tried this on several Worksheets in the same Workbook. In about 25% of the sheets I get #Value Error. I can not figure out why I get this on some sheets and not others.
August 6, 2008 at 10:50 pm
TQ
What’s wrong with these to find the last row?
```
'
Range("a65536").End(xlUp).Address or Range("a65536").end(xlup).Row
'
```
August 9, 2008 at 12:15 am
JP

Not sure what you mean. Add rows where? Just adding blank rows to a worksheet wouldn’t change the formula. I pasted this function into a standard module, and when I insert rows in the middle of a set of rows (which would actually change the value of the last used row), the formula recalculates.

HTH,
JP
September 9, 2008 at 3:06 pm
admin

hi all thanks for your sharing, i’d been busy for some projects in office

JP, looks like your method is far better then mine, thanks

TQ, nothing wrong with your method, i already try it too and its work great, thanks
September 25, 2008 at 5:26 am
VL

JP, that only works for visible cells.
Try it with the autofilter, it doesn’t gave the actual last row.
October 18, 2008 at 2:08 am
JP
@TQ: They’re version specific. You’ll want to avoid hardcoding the last row, to allow Excel 2007 users to use it.
```
'
Range("A" & Rows.Count).End(xlUp).Row
'
```
@VL: You’re right. Even the original code won’t do that. You’d need something like a function to check if a filter is applied to the worksheet, record the filter settings (a la http://www.j-walk.com/ss/excel/usertips/tip044.htm), then temporarily remove the autofilter and use one of the methods already show on this page to get the count. Then reapply the filter. If anyone’s interested I can try writing this function.

–JP
October 31, 2008 at 4:24 am
Don

TQ’s method assumes that the most rows possible is 65,536 rows. In Office 2007 this restriction was lifted. A revised method which works will all versions of office is:

Function LastRow(Col As String)
LastRow = Range(Col & Rows.Count).End(xlUp).Row
End Function

December 13, 2008 at 7:40 pm
Nuovella

'
'==========================================
'Find the last used Cell on a Worksheet - Robust version
'==========================================
Function FindLastCell() As String

    Dim afRange As String, afRangeNrows As Long, afRangeNcols As Long
    Dim afRangeLastRow As Variant, afRangeLastCol As Variant
    Dim lastRow As Variant, lastCol As Variant

    lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    lastCol = Cells.SpecialCells(xlCellTypeLastCell).Column

    If (ActiveSheet.FilterMode = True) Then
        With ActiveSheet.AutoFilter.Range
            afRange = .Address
            afRangeNrows = .Rows.Count
            afRangeNcols = .Columns.Count
            afRangeLastRow = .Rows(afRangeNrows).Row
            afRangeLastCol = .Columns(afRangeNcols).Column
        End With

        If (lastRow < afRangeLastRow) Then
            lastRow = afRangeLastRow
         End If

        If (lastCol < afRangeLastCol) Then
            lastCol = afRangeLastCol
        End If
    End If 'filter mode is on

    'MsgBox Cells(LastRow, LastCol).Address
    FindLastCell = Cells(lastRow, lastCol).Address

End Function

December 13, 2008 at 7:51 pm
Nuovella

'
'============================================
'Find the last used Cell on a Worksheet - Robust version2
'============================================
Function FindLastCell() As String

    Dim afRange As String, afRangeNrows As Long, afRangeNcols As Long
    Dim afRangeLastRow As Variant, afRangeLastCol As Variant
    Dim lastRow As Variant, lastCol As Variant

    lastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    lastCol = Cells.SpecialCells(xlCellTypeLastCell).Column

    If (ActiveSheet.AutoFilterMode = True) Then
        With ActiveSheet.AutoFilter.Range
            afRange = .Address
            afRangeNrows = .Rows.Count
            afRangeNcols = .Columns.Count
            afRangeLastRow = .Rows(afRangeNrows).Row
            afRangeLastCol = .Columns(afRangeNcols).Column
        End With

        If (lastRow < afRangeLastRow) Then
            lastRow = afRangeLastRow
         End If

        If (lastCol < afRangeLastCol) Then
            lastCol = afRangeLastCol
        End If
    End If 'filter mode is on
    MsgBox afRange
    'MsgBox Cells(LastRow, LastCol).Address
    FindLastCell = Cells(lastRow, lastCol).Address

End Function

December 13, 2008 at 9:55 pm
Nuovella

'
'=======================================================
' Find the last used Cell on a Worksheet - Robust version 3 by Nuovella
' This the simplest and best - it is robust against any type of hidden cells,
' hidden rows, hidden columns or filters
'=======================================================
Function FindLastCell() As String

  With ActiveSheet.UsedRange
            MyUsedRange = .Address
            nUsedRows = .Rows.Count
            nUsedCols = .Columns.Count
            lastRow = .Rows(nUsedRows).Row
            lastCol = .Columns(nUsedCols).Column
    End With
    FindLastCell = Cells(lastRow, lastCol).Address

End Function

February 10, 2009 at 9:28 am
Vera

Please I need vba to find the last cell in column A that is not blank but is not empty. I have formulas from A14 to A35 that returns a number or “”. I need the code to find the last cell in range A14:A35 that has a number. All that I found on the net will return A35 because it has a formula in it, even if the result is “”.

Thank you.
February 10, 2009 at 9:40 am
Vera

Correction
I need vba to find the last cell in column A that is blank (“”) but is not empty.
March 31, 2009 at 10:21 pm
anand

Can anyone please let me know whether it is possible to take the values from workbook B to workbook A without opening workbook B?
April 22, 2009 at 10:25 pm
Mike

Thanks, Nuovella!
June 8, 2009 at 5:09 pm
Poer

Thanks Nuovella >:D<

Excel VBA Macro