How to find Last Row in Excel VBA

0

There are several ways to find last row in the excel VBA. Finding last row is very important in the Excel VBA programming to work with ranges in spreadsheet. Based on your user or client requirement, you can use the below any one of the method in order to get last row value into one variable. The same can be used in particular worksheet in excel macros.

Usedrange.Rows.Count :

In case if you are going to update data in the worksheet by yourself as part of your automation, you can use this method to get last row of the particular excel spreadsheet.  However this method can be used only when you create new worksheet or created by your program related worksheet.

Example:

Dim Lastrow as long

Lastrow= Activesheet.usedrangerows.count

Or

If worksheet name is Summary then, you can define as shown in the below

Lastrow= Sheets(“Summary”).usedrange.rows.count

Or

Lastrow=Worksheets(“Summary”).usedrange.rows.count

 

Advantages of Usedrange.Rows.Count :

There is no requirement to specify any particular column or column letter to grab last row in this method. You can directly assign to one variable and use the same in the respective worksheet.

Disadvantages of Usedrange.rows.count:

If you use this predefined method in existing input excel spreadsheet, there are chances to get incorrect last row. Sometimes if excel spreadsheet downloaded from web portal or any other source, you get some garbage values in sheet.

Cells(rows.count,columnnumber).end(xlup).row:

 Using this also you can get last row and use the same in entire VBA program for the respective worksheet.  To use this, you should have non blank cells in any one particular column range. Then we can use that column number in this method as shown in the below example

Dim Lastrow as long

(Note: Better to define lastrow as long. It is best practice, because number of rows can be exceeded integer datatypelimit. If you know that rows compulsorily lesser than 32K, then you can go with integer)

I am assuming 1st column has non blank cells within the range)

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,1).end(xlup).row

Advantages of cells(rows.count,columnnumber).end(xlup).row:

This method always provides you accurate value of last row in particular column range. Hence you can use this in input files in excel macro or VBA.

Disadvantages of cells(rows.count,columnnumber).end(xlup).row:

We have to take care to use non blank cells column. Else there is a chance to get incorrect value into last row.

Range(rows.count,”columnletter”).end(xlup).row:

 In this method also we should have at least with column range with non blank cells.

Dim Lastrow as long

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,”A”).end(xlup).row

Note: Here I consider column “A” has non blank cells.

LEAVE A REPLY

Please enter your comment!
Please enter your name here