How to find the last row and column using VBA in excel

find-last-row-column

Why find the last used row?

Finding the last used row helps in many ways, if you want to append data to list using VBA it is required to know the last used row or column to append data. Through this, we get to know the last used row number or last used column name. Thus finding last used row or column makes useful in many ways.

There are many ways to find the last row and column in VBA we will see some of them with examples.

Method 1:

Code: Range("A" & Rows.Count).End(xlUp).Row

find-last-row-column

In this method .row property returns the last row number only. The above code finds the last row having data, as you can see the code checks for column A's last row and finds it as 13 and for displaying the result Msgbox() function is used.

Method 2:

Code: Range("A" & Rows.Count).End(xlUp).Select

find-last-row-column

The .select property selects the last used cell in a row or column. As you can see the last used cell in column A is A13 which is selected. Once the cell is selected you can style the cell, copy to another cell or sheet, append row or column, etc can be made.

Method 3:

Code: Range("A" & Rows.Count).End(xlUp).Address

find-last-row-column

The .address property returns the address of the last used cell in a row or column. As you can see it returned last used cell A13's address as $A$13.

Last used column

So far, we have seen how to find last used rows with different properties, now we will see how to get the last used column.

Method 1:

Code: Cells(1, Columns.Count).End(xlToLeft).Column

find-last-row-column

This property .column is same as .row,row property returns row number but column property returns column number.these row and column properties can be used to find the number of used rows as well as used columns.

The basic syntax for cells object is cells(row index, column index).In the above code we have 1 as row index and columns count as column index, as you can see for the row index 1 last used column is C which is having column index 3.

Method 2:

Code: Cells(5, Columns.Count).End(xlToLeft).Select

find-last-row-column
Method 3:

Code: Cells(4, Columns.Count).End(xlToLeft).Address

find-last-row-column

we requested to find last used column's cell address in row number 4, so the last used cell in row number 4 is in cell address C4 obviously it returned that cell's address.

Summary

To summarize what we learned, we learned about finding last used cell in a row or column secondly various properties like select, which selects the cell and Address property which returns last used cell's address and Row property which returns row number then for column number column property can be used.

Hope you understood, you can also download this project file and for more detailed demo watch the below video link.

Try experimenting with these codes to get clarity.

Excel Logo

Download project file

Video: How to find the last row and column using VBA in excel

Share on:
Most Popular