How to get the Last Row in VBA(The Right Way!)

One of the most common VBA questions is how do I get the Last Row with data. And the most common answer to this question is normally wrong.

In this video I'm going to show you the 5 methods for getting the last row and which is the best. I'm also going to give you some simple VBA functions that will do the work for you.

All the code used in this video is available to download from the link in the description below.

#VBALastRow #ExcelLastRow


Useful VBA Shortcut Keys
========================

Debugging:
Compile the code: Alt + D + L OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)

Windows:
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
View the Properties Window: F4
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R

Writing Code:
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y(note: this clears the clipboard)

Table of Contents:

00:00 - Introduction
03:12 - UsedRange
05:28 - Special Cells
08:19 - End (xlUp)
11:27 - Range.Find
13:54 - FindLastRow Functions

[Excelmacromastery]

Let me know what you think about the methods in the comments. Make sure to download the source code with examples and the custom functions that I created.

[Metalocif]

Didn't know you had a Youtube channel. I teach VBA, and I always point my students to your website as the best resource there is.

[PraiseTheLord]

How dare you defeat all our "tried and true" methods! ;) Thanks for the video

[charlieprocaccini]

The find method is an interesting way to approach this problem and it works well in most circumstances. But in testing I did find that if you' are working with a protected sheet that also has the Hidden checkbox selected, the functions will generate an error and return Cells (1, 1) from the error handling code. If you manually hide rows or columns the functions work correctly.
Also, if filters are turned on you will get the last filtered row, not the last of all rows. Returning the last filtered row could potentially be useful as long as you are aware and not looking for the last actual row of data.

[thairtalib]

Thanks Paul for sharing such a great job, no one like you .. no one ever .

[free]

Such an elegant solution, thank you for sharing your wealth of knowledge!

[rogerirvine]

In the final example, I love the fact that you can (if required) insert some starting point coordinates. Excellent. Seeing your patient build-up was worth the wait. Thanks Paul. I've learnt a load of real, 'outside the box' approaches to many things that are useable and adaptable in 'real world' settings since I signed up for your course. (And I've promised myself that I WILL get to finish it!)

[KurtSchwind]

I've probably solved the 'last Row' or 'last Column' a dozen ways and none of them are as clean and consistent as that 'Find' method. Thanks.

[YDysgwrAraf]

Working with and navigating our way around unstructured data is an absolutely key skill for spreadsheet developers, and all of these techniques have a place in out toolbox. Knowing the appropriate tool for the job in hand is the crucial thing to learn.
I recently worked on a Java project where we were reading spreadsheets into Java library objects, and my fellow developers were astonished by the ease with which I could, using knowledge from years of work with Excel, find my way around the jagged and unpredictable data.

[TheGallantTitan]

I struggled with the issue of not properly selecting an area of Jagged data until I watched this video. Thanks for helping me out.

[hotflashfoto]

Most of the macros I manage were inherited, and thankfully they have column A fully populated with no spaces.

However, they were written in the days when there were only 65, 536 rows. Recently, there was data that went past 70, ooo rows and it decided that the last row was row #1 until I updated it.

At first, I was skeptical of a better way, but using Find looks like a good way of doing it regardless of sheet length. Thanks for sharing!

[joukenienhuis]

Thank you for this very helpful video. I think the lastRow function is especially handy when you want to enter new data. But then again if you work with named Data Ranges in Excel, it can automatically add the last entered data if you are using a form

[eduarchavarria]

Paul, great as always, thanks. Searching for the last row of data is one task that I perform very often.

[rjaquaponics]

This video was incredible. It's like juggling, while smoking a cigar, while riding a bike, while skiiing down fresh powder all at the same time! Bravo!

[monochromaticspider]

Missed a big one with .end(xlUp). It works on VISIBLE rows. So if you have filter mode on and some content rows are filtered out then you're not actually finding the last content row, just merely the last visible content row. Certainly something to keep in mind. Still, this is my general goto because of how quick and simple it is.

As for Find, I suspect (without having checked) that it has some performance overhead. It uses the regular workbook "Find and Replace" function to look through content but of course stopping after the first result of literally anything should help. Do keep in mind that the search term shows up if users manually open the Find and Replace function.

[johnwayne]

That's simply great! Well done Paul!👍👍👍

[zaksept]

Perfect timing, I was just doing a job today and I needed to copy 30 sheets into one.
They all had different amount of rows ranging from 20 - 300. However, xlDown woudn't work because the last row had data in random columns, not always col A.
What I ended up doing was a for loop where I pulled 300 rows from each sheet, and paste them into the summary sheet. Afterwards I ran a separate macro to delete the blank lines with nothing on them. This would have saved me a couple steps.

[woodpigeon]

Good one Paul. It’s one of the first question you ask yourself when you are writing Excel code.

[mariaalcala]

Great method to find the last row! Thanks for sharing your knowledge!!!

[renemarot]

Thanks. Very didactic. Nerver thinked about using find this way.
One cave at with find is hidden rows and hidden columns. If you have some of them find just seems to ignore them.