Get the Last Row using VBA in Excel

Add to Favorites
Author: | Edits: don

(file used in the video above)

How to find the last row of data using a Macro/VBA in Excel, including getting the number of that row and any data from that row.

This tutorial shows you the small line of code that will get you the actual row number for the last row of data in Excel and also, if you want, the data from that row.

Sections:

Get the Last Row Number

Select the Last Row

Get the Last Row Data

Notes

Get the Last Row Number

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

b2663f1a613ee8e40b09a32256ce3343.jpg

Change the A to whatever column you want to use to find the last row and that's it.

To do something with the row, put it into a variable like this:

d89d1140d5ccaceaac18d502a15a590c.jpg

last_row is now the variable that will hold the number of the last row of data in column A. You can change the variable name to whatever you want.

Select the Last Row

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

88547b45d72e611a36f2e6ce141a80dd.jpg

Change A to the column that you want to use to find the last row of data.

This will select only the cell that is in the column that you specify, column A in this case.

Select the Entire Row

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

ffdaaf645e28829646ce414d6f1349f2.jpg

Get the Last Row Data

Range("A" & Rows.Count).End(xlUp).Value

5ab36b884d415686267a502ad75eb0f8.jpg

Change A to the column that will be used to find the last row; this column will also be the column from which the data is gotten.

Now that you have the value, you will probably want to do something with it; so we should put it into a variable so the value is easy to use.

b401e5fd3a243bfb1a0bc34fc973f234.jpg

last_row_data is now the variable that will store the data from the cell that is in the last row of the data-set and the column that is specified, A in this case. You can change the variable name to whatever you want.

Notes

All of these examples use the same base: Range("A" & Rows.Count).End(xlUp) and the only difference is what is put behind this: row, value, select, or entirerow.select.

The final macro in the included file has all of the above examples so you can clearly see, compare, and become familiar with this code.

Download the attached file to work with these examples in Excel.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Find the Last Column with Data in Excel VBA
Tutorial: How to find the last column in a row that has data. This includes selecting that column or...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Remove Dashed Lines from Copy Paste VBA in Excel
Tutorial: How to remove the flashing dashes from a copy/paste range using VBA in Excel; this removes...
Calculate the Difference Between Two Times in Excel
Tutorial: Here, youll learn how to get the difference between two times in Excel. A common example...
Get the Last Day of the Month in Excel
Tutorial: How to get the last day of the month, including the date and day of week, for any date in ...
Activate or Navigate to a Worksheet using Macros VBA in Excel
Tutorial: Make a particular worksheet visible using a macro in Excel. This is called activating a wo...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course