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.
Code: Range("A" & Rows.Count).End(xlUp).Row
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.
Code: Range("A" & Rows.Count).End(xlUp).Select
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.
Code: Range("A" & Rows.Count).End(xlUp).Address
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.
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.
Code: Cells(1, Columns.Count).End(xlToLeft).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.
Code: Cells(5, Columns.Count).End(xlToLeft).Select
Code: Cells(4, Columns.Count).End(xlToLeft).Address
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.
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.