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.
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
Pingback: How To Find the Last Row That Contain Data in Excel? - Learn Excel
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.
What’s wrong with these to find the last row?
' Range("a65536").End(xlUp).Address or Range("a65536").end(xlup).Row '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
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
JP, that only works for visible cells.
Try it with the autofilter, it doesn’t gave the actual last row.
@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
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
' '========================================== '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' '============================================ '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' '======================================================= ' 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 FunctionPlease 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.
Correction
I need vba to find the last cell in column A that is blank (“”) but is not empty.
Can anyone please let me know whether it is possible to take the values from workbook B to workbook A without opening workbook B?
Thanks, Nuovella!
Thanks Nuovella >:D<
I want to add a row with a condtion
if in column a the last date is not current date then insert date
can any one help me
I am working on a function to do statistical analysis. I have been able to get most of the portions of the code to work fine except for counting the number of rows in the selected array:
Function Analysis(Data)
N = Data.Rows.Count
The count is retuning all of the rows in the array, regardless of if they have information in them or not.
Pingback: Coupons For Groceries
Pingback: debt consolidation companies
Pingback: reverse mortgage definition
Pingback: debt consolidation company
Pingback: debt relief
Pingback: credit card debt relief
Pingback: buy youtube views
Pingback: railing planters