Finding last row or column in a worksheet using VBA
Do you want to find out the last nonempty cell in both vertical (row) and horizontal (column) direction.
You can implement the following vba code in your script to detect the last row/column in a Excel worksheet. There are other ways as well to find this in a Worksheet, which uses Usedrange or Currentregion properties.
But we have used this function in most of our projects & it just works fine. So, lets get the code.
Finding Last Row in Excel Worksheet
The below code returns the last row number that is non empty. It takes the parameter as a range. So, You can either input the full sheet as range or a spcific column as well.
The sample function has 2 example illustrating the same.
'Function to test the code Sub testGetLastRow() 'Get last row in whole worksheet Debug.Print getLastRow(Sheets(1).Cells) 'Get last row in a specific column Debug.Print getLastRow(Sheets(1).Columns(1)) End Sub 'Function that returns the Last row in sheet or range Function getLastRow(iRng As Range) As Double 'Define Dim fRng As Range, iLastRow As Double, iLastCol As Double 'Find for last row that is non empty Set fRng = iRng.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not (fRng Is Nothing) Then iLastRow = fRng.Row 'Return Last row getLastRow = iLastRow End Function
Once you run the first function, You can see the output in the immediate window using the Ctrl + G keys.
If you want to ind out the last column in addition to the last row, then use the function in the next section.
VBA Code – Find Last Row & Column
Copy the below code to a modules & you can call this function from your code. The function returns both last row & column in a variant array.
Function findlastRowLastColumn(shName As String) As Variant Dim vRet(1 To 2) As Double Dim iLastRow As Integer Dim iLastCol As Integer Dim iSh As Worksheet Dim fRng As Range 'Init Set iSh = Sheets(shName) Set fRng = iSh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious) If Not (fRng Is Nothing) Then iLastRow = fRng.Row Set fRng = iSh.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) If Not (fRng Is Nothing) Then iLastCol = fRng.Column 'Return vRet(1) = iLastRow vRet(2) = iLastCol findlastRowLastColumn = vRet End Function
If you would like to know more about how to do this refer to the below link as well.
External Link: Way to find last row or column in a worksheet