Select The Last Used Cell In A Column – Excel VBA

It’s quite common to need to know the last used row in a sheet or column in a spreadsheet. You may want to add more data to the end of a column or you may need to select a range of used cells.

There are quite a few ways to find and select the last row in a column using VBA and each have their merits. Here is the one I use the most…

Create a variable to store the row number

Dim LastRow As Long

(Notice that I use the ‘Long’ data type. You could use an ‘Integer’ data type if you’re never going to reach 32,767 rows. The ‘integer’ data type stores numbers through -32,768 to +32,767 but ‘Long’ goes far beyond excel’s row limit). We used to use integer data type until one of our macros broke and it took us 30 minutes to work out that it was because the last row in the column was over 32,767.

Then assign LastRow a value with this VBA code…

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

This will store the row number of the last used cell in column A. Change the column letter to whatever column your interested in.

Then to select the last used row in column ‘A’ use…

Range("A" & LastRow).Select

To select the next empty cell simply add one…

Range("A" & LastRow + 1).Select

If you don’t want to store the row number in a variable you can go straight to using …

Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select

If you do this you will not need to declare a variable (LastRow) as we have done above. You wont have it stored to retrieve it later on if needed.

4.44/5 (9)

Please rate this

0 comments on “Select The Last Used Cell In A Column – Excel VBAAdd yours →

Leave a Reply

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