FIND THE LAST ROW IN A WORKSHEET USING VBA

Use the following code to find the last data row in a worksheet.


Get Last Data Row –

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

'Method Overview '*********************************************************************** 'name: GetLastDataRow 'params: Worksheet 'returns: none 'created: 01/04/2013 'reviewed: 'comments: To calculate the last data row of a worksheet '*********************************************************************** Public Function GetLastDataRow( _ ByVal TargetSheet As Worksheet _ ) As Long On Error Resume Next GetLastDataRow = TargetSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row End Function

Usage –

1 2 3 4 5 6 7 8 9

Sub Test() Dim lngLastRow As Long lngLastRow = GetLastDataRow(Sheet1) 'In this line, Sheet1 is a worksheet codename 'or lngLastRow = GetLastDataRow(ThisWorkbook.Sheets("Sheet1")) 'In this line, Sheet1 is a worksheet display name End Sub

You can also use integer type to catch the return value from a function until and unless the return value does not exceed the limit of integer.

Please share your thoughts on this post in the comments section.

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.