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

Matteo