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.

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.

More Excel Examples

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

More Excel Examples