Approach to find last row or column using VB script in Excel?

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

Finding last row of excel using code

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
'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")

'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

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

2 replies on “Approach to find last row or column using VB script in Excel?”

I tried to find the last used column in a row, using the following code:

iLastCol = oCountWS.Cells(1, oCountWS.Columns.Count).End("-4159").Column

However, getting unknown error.
Can you help ?

Leave a Reply

Your email address will not be published. Required fields are marked *