Approach to find last row or column using VB script in Excel? – User friendly Tech help

We are sharing these techniques so that we can work with dynamic set of data in an Excel.

n

n

nDo follow us for more learning’s Fb,G+,Twitter.
n
nTechnique 1:- 
nUsing UsedRange property:- 
nThis approach is frequently implemented and one of our favorite. But sometime we have seen that incase some formatting is present in the sheet, it points till the last range of the excel (like 1,048,576 rows in excel -2013) itself rather than the last occupied row of the sheet. 
n
nTechnique 2:-

n

    n

  • Using “CTRL+SHIFT+END” to find the last row 
  • n

  • Using “CTRL+SHIFT+Down Arrow” to find the last row 
  • n

  • Using “CTRL+SHIFT+left/Right arrow” to find the last column
  • n

n

Code:-

n

'*******************************************Function*********************************
'Function Name:- fn_CalRange
'Function Description:- Function to Calculate the last row & last column
'Input Parameters:- N/A
'Output Parameters:- N/A
'************************************************************************************
Sub fn_CalRange()
Dim iLastRow
Set objExcel = CreateObject("Excel.Application")
'Making Excel visible to user
objExcel.Visible = True
Set oCountWB = objExcel.Workbooks.Open("D:Test.xlsx")
Set oCountWS = oCountWB.Worksheets("Sheet1")
'****************UsedRange********************
'Counting the Last row using 'Range' Property iLastRow = oCountWS.UsedRange.Rows.Count
'Counting the Last Column using 'Range' Property iLastCol = oCountWS.UsedRange.Columns.Count
MsgBox "Using Range Property Last Row: " & iLastRow & " Last Column:" & iLastCol
'*****************CTRL+SHIFT+END*************
'-4162 meaning xlUp
'Using CTRL+SHIFT+END to find the last row
iLastRow = oCountWS.Cells(oCountWS.Rows.Count, "A").End("-4162").Row
MsgBox "Using CTRL+SHIFT+END, Last Row:" & iLastRow
'*****************CTRL+SHIFT+Left Arrow*************
'Using CTRL+SHIFT+left arrow to find the last Column
'-4159 for xlToLeft
iLastCol = oCountWS.Cells(1, oCountWS.Columns.Count).End("-4159").Column
MsgBox "Using CTRL+SHIFT+Left Arrow, Last Column:" & iLastCol
'*****************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 Sub

n

More Excel Examples

Was this article helpful?
YesNo

Similar Posts