Excel Macros : Find Last Row

Many times we need to find out the last row of excel sheet which is used by data. This has many uses such as to

Add new column to the data only up to the last used row of exiting dateAdd new row to the data immediately after the last row so as you do not over write the exiting data.

See the code below for the doing the activities as listed above.

Sub LastRowOfData() '*************************************************************************************************** '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 4, 2009 * '*************************************************************************************************** If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False End If LastRow = GetLastRowWithData ' Getting the Row number Range("C3").Select ' Select the header row of the data in new column ActiveCell.FormulaR1C1 = "Col 2" ' Add new column header Range("C4").Select ' Select the starting column where you need to add data ActiveCell.FormulaR1C1 = "XYZ" ' Add Data Value or Formula Range("C4:C" & LastRow).Select ' This will select the column from starting row to Last row of data Selection.FillDown ' This will fill the data downwards from First row i.e. Formula or value added by you Range("B" & LastRow + 1).Select ' This selects the starting cell of row next to last row of exiting data End Sub '******************************************************************** '* UDF to Get Last Row with Data on 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

Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.

Download sample file to check how it works.

There is one more option which gives you satisfactory results

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Take care to remove filters before using above code

There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
or
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row

Select Last Row , Find Last Row , VBA Last Row , VBA Find Last Row , Excel Last Row