Approach to find last row or column using VB script in Excel?
We are sharing techniques to work with a dynamic set of data in Excel.
Technique 1: Using UsedRange Property
This method is commonly used and is one of our favorites. However, sometimes, if there’s formatting in the sheet, it points to the last range of the excel (like 1,048,576 rows in Excel 2013) rather than the last occupied row of the sheet.
Technique 2: Keyboard Shortcuts
- Use “CTRL+SHIFT+END” to find the last row.
- Use “CTRL+SHIFT+Down Arrow” to find the last row.
- Use “CTRL+SHIFT+left/Right arrow” to find the last column.
Code:
Function fn_CalRange()
Dim iLastRow
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set oCountWB = objExcel.Workbooks.Open("D:Test.xlsx")
Set oCountWS = oCountWB.Worksheets("Sheet1")
' Using UsedRange Property
iLastRow = oCountWS.UsedRange.Rows.Count
iLastCol = oCountWS.UsedRange.Columns.Count
MsgBox "Using Range Property Last Row: " & iLastRow & " Last Column:" & iLastCol
' Using CTRL+SHIFT+END
iLastRow = oCountWS.Cells(oCountWS.Rows.Count, "A").End("-4162").Row
MsgBox "Using CTRL+SHIFT+END, Last Row:" & iLastRow
' Using CTRL+SHIFT+left arrow
iLastCol = oCountWS.Cells(1, oCountWS.Columns.Count).End("-4159").Column
MsgBox "Using CTRL+SHIFT+Left Arrow, Last Column:" & iLastCol
' Using CTRL+SHIFT+Down/Right Arrow
iLastRow = oCountWS.Range("A1").CurrentRegion.Rows.Count
iLastCol = oCountWS.Range("A1").CurrentRegion.Columns.Count
MsgBox "Using CTRL+SHIFT+Down/Right Arrow,Last Row:" & iLastRow & " Last Column:" & iLastCol
End Function
Was this article helpful?
YesNo