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

Do follow us for more learning’s Fb,G+,Twitter.**Technique 1:-** *Using UsedRange property:-*

This 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. **Technique 2:-**

- Using “
*CTRL+SHIFT+END*” to find the last row - Using “
*CTRL+SHIFT+Down Arrow*” to find the last row - Using “
*CTRL+SHIFT+left/Right arrow*” to find the last column

**Code:-**

'*******************************************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