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 the last row in a Worksheet, which uses Usedrange or Currentregion properties.
But we have used this function in most of our projects and it just works fine. So, lets get the code.
VBA Code – Find Last Row & Column
Copy the below code to a modules and you can call this function from your code. The function returns both last row and column in a 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
Line 3 – 6 are for declaring the variables:
• iLastRow; this will contain the last nonempty row number
• iLastCol; this will contain the last nonempty column number
• iSh; used to specify the worksheet you want evaluated
• fRng; a mnemonic for doing the evaluation
You need to replace the name of your worksheet at line 8.
Line 10 and 11 will look through all the rows, starting from row 1048579 going backwards, to find the first nonempty row.
Line 13 and 14 will look through all the columns, starting from column XFD (count 16384) going backwards, to find the first nonempty column.
Line 16 and 17 will produce dialog box with the last row and column number.
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