How to get the Last Row in VBA(The Right Way!)
Вставка
- Опубліковано 16 лип 2024
- Learn how to write real-world Excel VBA code: 👉courses.excelmacromastery.com/
Want to download the source code for this video? Go here: bit.ly/3cqvT0o
Subscribe to the channel here: bit.ly/36hpTCY
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 - Наука та технологія
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.
Can you please also make Python channel. Sooner the better
pleas how I can download the source in this great video.
I have seen many videos but many codes just in your video especially to find the last cell and to select range with using the last row and last column as a reference to a cell. thank you for your work and how to download the codes and examples
Excellent.
One small problem - if the sheet is empty it will find nothing and give back a range with nothing -> Err = 91.
if range is nothing then
Last = 0
else
Last = range.row
endif
Thanks a lot for the video. I tried it out, and I think it does not count merged cells. So if the last unmerged cell is in C3 and there is a merged cell from D4 to E4 your last cell will be C3. Find function does not properly work with merged cells I think.
Didn't know you had a UA-cam channel. I teach VBA, and I always point my students to your website as the best resource there is.
Thanks Alexandre.
Thanks Paul for sharing such a great job , no one like you .. no one ever .
Paul, great as always, thanks. Searching for the last row of data is one task that I perform very often.
Such an elegant solution, thank you for sharing your wealth of knowledge!
How dare you defeat all our "tried and true" methods! ;) Thanks for the video
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.
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.
Thanks for your note!
Great and useful help for those who do heavy programming in VBA-Excel. Thanks Paul.
You're welcome
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!)
Glad you like it Roger.
Irvine, CA?? Also in OC!
@@Excelmacromastery hi sir Paul, i love Excel, and i starting to learn about VBA, from Where i Begin?
Great method to find the last row! Thanks for sharing your knowledge!!!
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
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!
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.
That's simply great! Well done Paul!👍👍👍
Thanks John
I struggled with the issue of not properly selecting an area of Jagged data until I watched this video. Thanks for helping me out.
Good job Paul! Seriously going to be helpful with a future project.
Thank you so much for sharing this great video. The knowledge you share is priceless.
Good one Paul. It’s one of the first question you ask yourself when you are writing Excel code.
Excelente aula, parabéns!
Another great video Paul!! Very informative! Thanks so much 👍🏻😃
Thanks Scott.
As usual, great choice of topic for a video and good solution for the problem..
Really looking forward to this, Paul!
I hope you'll like it Jim.
@@Excelmacromastery Like it? I love it, Paul! I interrupted workday to watch this instead of viewing in evening/weekend (usual practice), took detailed notes while watching, & already downloaded source code. I plan to add your code (modified to my naming & commenting practices) to a class module that I created for often-used subs/functions; it works like Access's DoCmd. Thank you for walking thru the first 4 methods before teaching Range.Find. I've used different approaches in the past, but now will standardize on your code. Thank you for sharing this with us!
this is absolute awesome!! thank you very much!!!!
Excellent presentation Paul !
Thank you so much! you dont have idea on how much this will help me
Excellent video. A nice addition would have been showing people how to turn the returned column number into a letter.
Great video Paul, thanks.
I have been waiting for this longtime.Thank you Mr Paul KELLY
You're welcome
Got the most wanted video at the right time.
really it was a great video that I saw. I saw many videos before but it was the best.
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.
Thanks! Very useful video.
Great Video! Thanks! 👍
🤯🤯🤯🤯 wow you never disappoint me in always bringing new knowledge to what i have been doing. Outstanding!!! Will try this method with my data. I always need to check the amount of data i have to deal with and this will help me a lot
Thank you!
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.
Glad it was helpful Zach.
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.
Well I thought about performance as well, but after all this are only 2 searches, so it shouldn't be so bad.
Wow! excellent video. Instant sub. Thanks!
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!
Thanks
CAN'T WAIT
Never thought of it using find for that, thanks
You're welcome.
Thanks for sharing.
Thank you so much
Actually, I have some sheets where I want to count cells with no data but only color format so the UsedRange "bug" is actually a feature. Very nice!
It does work perfect for that:)
For each cell in range? Could be 1 row, or column. Im using that since i've met with an anomaly (same method /end(xlup).row /different row values in col a or col b, last row were the same)
Wonderful videos !
Thanks
I long to see this premiere
Greetings from Santa Cruz Bolivi🇧🇴
Thanks Jose
Paul, please explain also how to get FIRST column, FIRTS row in range. Thanks in advance!
Thank you.
Thanks !!
Thanks
Very useful
@ Excel Macro Mastery...I have seen your videos on class modules but I would love more.......... not specific but about anything But organising application as a whole because I have faced problems while altering or adding features to already functioning applications..
Class Modules is a big topic. I may do more in the future on some of the concepts involved in designing classes.
Amazing must watch it again. Are you on linkedin?
Great 💯👍
absolutely brilliant.
note that we can use Clear All to remove formating rather than having to close the whole WorkBook? :-)
Thanks for the great content. Where was this video 15 years ago?
Genius!
the problem with USedRAnge is not so much the formated cell but when you clear (of formats as well as contents) the formatted cell.
It still remains in the USedRange unless you properly delete it ;-)
BTW: Excellent video as usual.
Glad you like it Ed
Great video as always Paul!
BTW, just think that method could be slightly improved too….
Firstly, I’d run a “dummy Find” afterwards in the Function, to set the Find arguments back to default (otherwise, next time the User tries to use Find in that session, they will unwittingly use these quirky argument settings of course).
Also, the other small downfall I find with this method is that Find will not work on a protected sheet if the cells formulas are set to Hidden (even if the cells have just values, it still won’t work).
A way around this is to check using CountA, this would normally be too slow to loop though each row from the last row backwards, but I find that you can get this to run really quickly by using a loop that uses a “shrinking range” as you check.
Thanks Martin. Some good ideas there.
@@Excelmacromastery no probs Paul, btw I work in VBA pretty much full time, and your videos have really helped me out along the way. Big thanks:)
Excellent compare and contrast. Do you have advice on how to handle/trap those cases where the range you're testing might be empty?
NM. Your eh: captures the situation perfectly.
tsk you so much :D
could you please tell what's the screen recording tool you used? it seems perfect.
Excelent.
Interesting as always, but I do wonder if this is a bit of a sledgehammer to crack a nut. When reading in data that may have lots of blank cells, I design the sheet so that it has a row of headers at the top, with no gaps, and then a "ref" or similar field in the first column which is non-empty for every valid record, and a blank cell denotes the end of the data. (one could automate this of course, using code on the sheet to detect when changes are made and check column 1 is non empty, unless the cell below contains "ZZZ"). Then I use something like set rg = range(sh1.range("top_left").end(xltoright), sh1.range("top_left").end(xldown)), although I think current region would work fine and normally give the same results (except where one has a row with a blank in column 1...).
hi thank u for the lesson. idk how to code for my project and need help.
2 given arrays are list of discounts for office supplies and furnitures
Using macro, we needa extract the correct discount from the 2 arrays and match it with the correct product in excel sheet. to be coded into a blank row of discount for the lists of products in excel sheet.
Great insights into the failings of native methods and very useful method to get around them. Extremely useful. Thanks for sharing your insights with the community.
Do your methods for finding the last row and last column work with merged cells or hidden rows/columns?
Nice work! But how effectively to use Find method in code?
Hi Paul. Great tutorial! I tend to use the Range.End method in simple cases when I know the column or row to test and the Range.Find method when needing something more bulletproof. I've seen the Range.Find method written including the argument: After:=Range("A1") to force the search to start from the last row / col of the worksheet, given that the SearchDirection is defined as xlPrevious. If the After:= argument is omitted, is there a default starting point.. maybe from the current location of the pointer? Just curious if it is needed and or makes a difference. Thanks again for all of your great videos. Always learn something fun and new :)) Thumbs up!!
Thanks Wayne. The default starting cell(i.e. After) is the Top Left cell of the range. If you are using the Excel Find Dialog then the default After cell is the active cell. See excelmacromastery.com/excel-vba-find/#Using_After_with_Find
@@Excelmacromastery Awesome! Thanks Paul :))
This the first video that has genuinely made me interested in the course. I'm just beginning with VBA and the info is so disjointed typically
how to do ctrl+arrow down as loop until last cell found in vba? that would be appreciated
I have multiple sheets in one workbook.I need to combine all sheet as one master sheet and whatever I modify in master sheet it should reflect changes in respective sheet automatically
Paul, this was an excellent video, thanks very much. However, the download link doesn't seem to be working, tried it for the last 3 days, just "spins".
I use shtWhatever.Cells(rows.count,1).end(xlup).row
Does it also work if the last row(s) with data are filtered or hidden and you stil wants to know the real last row on the sheet?
Great Video ! as always! Just can't download the Code, cause the site isnt loading. Update: it was my adblock. Your site ist now "excepted" :D
Would be good to know how autofilter affects any of the methods…
Last method has a slight disadvantage. It gives an error while there is no data at sheet instead of one. So, you can wrap up your function with an if statement wheter sheet contains data or not by using native counta function
Conclusion, there is no simple way to determine the last row or column containing data in Excel or VBA. There are ways, just no simple, reliable function unless you write one, or include a third party library.
Still, his was a useful video for several reasons:
1. It includes the code for such a function, which as you note, needs an exception handler for an empty worksheet.
2. It shows the strengths and weaknesses of the 4 commonly used methods. The fact that two of them don’t work on protected worksheets means I will never use those two methods. Not every worksheet is protected, but I refuse to use methods that will fail when I do have protected worksheets enabled.
3 .end(xlup) and end(xltoleft) look to be the most generally useful, simple functions, as long as you’re aware of their limitations regarding “jagged” data in the final row/column. When you can’t ensure the final row/column will always contain in one specific column/row, then Paul’s functions with your modification is the way to go.
Actually, this is a fabulous but heavy-to-digest lesson. I hope that you have used explicit variables and meaningful sheet names and did not use the dot style for writing the collections of the functions to make it easier for us to understand. Overall, Great work. Thanks.
Hi - Find method doesn't work if data on a sheet is filtered, in that case we need to clear the data but since sheet is protected so find will also not work, so we can write another function based on usedrange and counta worksheet function that will work in each situation
I can't congratulate you correctly, because my bad english, but I can say: clap, clap, clap. Thanks
To me, the major issue with xl(up/down) method is that it ignores hidden rows. I also use find -- the only draw back is that it messes with my settings the next time, but that's an acceptable penalty compared to all the others.
I just created a function that resets the settings. Problem solved.
Can you please make a separate channel for python only?
How can I find the last row if there is an expanded array formula in the longest column?
Could you please explain the meaning of "on error goto eh"?
As always, great tutorial Paul. I got excited at seeing functions which will allow me to protect sheets and return "currentregion" which otherwise fails. But, cannot seem to download the code. Have no ad blockers active. Link sends me to a youtube page with your icon for watching the video. Tried creating the functions myself, but i'm missing something. Any solution would be much appreciated. I really need these functions.
Send me an email and I'll send you the code. Paul ExcelMacroMastery
does it work in listbject ?
I am getting a subscript out of range error with the cells.find method
Why no speed comparison? That's the biggest thing I care about in such function atm
another excellent video, thank you. One quick question; you were using the shJagged to refer to the sheet "Jagged data" how does this work? I was expecting you to have to type worksheets("jagged data") but ShJagged is much cleaner.
Option Explicit
Private Const cTxt as string = "I found: "
Public wb as Workbook
Public ws as Worksheet
Public sub exmplSub()
Dim rng as Range
Dim STxt as String
Set wb = Thisworkbook
Set ws = wb.sheets(1)
Set rng = ws.cells.find("*")
If Not rng = Nothing Then
STxt = rng.value2
Elseif ws.Usedrange.Cells.Count
Apologies if I'm being annoying but I wanted to give an example of what I meant by explicit declarations and the module and procedural level.
Please also forgive me if 2 more things: 1
If I assumed too little of your knowledge; and 2 if that example isn't perfect. Im pretty sure it's right. But I just hand typed it on my phone directly into the comments lol.
I hope you or literally anyone reading this finds it helpful even a tiny tiny bit. Im gonna go now 😅
@@coloringontheline Thank you Zachary for replying however I am sorry that my skills don't allow me to understand how this response addresses my question about using shJagged to refer to the sheet "Jagged data".
@@miless2111sutube got it, okay. No worries. My fault. Let me see if I can try again. If this doesn't help maybe I can make a 1-2 min video on it or something. I'm not a UA-camr so it wouldn't be professional. But it might break the communication issue.
Here:
Windows Hotkeys. (Or use the View menu at the top of the VBA Editor window)
Property Explorer = Ctrl+R
Property Window = F4
"(Name)" is the first "Property" listed in the "Property Window" when you select "Sheet1" of your VBAProject from the "Project Explorer". "Sheet1" can be found when the section "Microsoft Excel Objects" of your VBAProject is expanded. Which by default, it should be.
Press Ctrl+R to select the Project Window. Use your Up and Down Arrow Keys to highlight "Sheet1." Then, Press F4. (not Alt+F4 lol, that closes windows) once you've pressed F4. The property window will either be selected if already open, or it will open if it was closed.
The item selected after pressing F4 is the "(Name)" property. You can either click on it, or simply press F4 again. Then, start typing a new name.
Whatever you name an objects "(Name)" here is called it's "Codename" and it is what's happening, and how he refers to the sheet as "ShtAnything"
So if the first Worksheet in your Excel is called "Catalog List"
And you changed the "(Name)" to "CataList"
You could use all of the following:
Worksheets(1).Range
Worksheets("Catalog List").Range
Sheets(1).Range
Sheets("Catalog List").Range
CataList.Range
You cannot refer to it as:
Thisworkbook.CataList.Range
Workbooks("Book1").CataList.Range
If you delete the worksheet. The (Name) property doesn't transfer to the next sheet. Meaning. If you use the (Name)/Codename in your code a lot. You'll want to not delete the sheet.
If you plan to delete it. Try using the Declarations I talked about earlier.
You can only change (Name)/Codename during "Design Time" meaning you can't make a macro change (Name). You have to type it by hand for each object you want to name.
It's not a recommended convention for programming for the reasons I mentioned earlier. However, that doesn't mean that you can't or shouldn't do it if it's what's best for you or your projects.
Happy to help more if this doesn't work out or if I didn't communicate well again.
Links below are to Microsoft Docs Reference Material about this question.
Note: this does mean that you can actually just use the default codename from the get-go.
Like,
Sheet1.Range
No change to anything necessary.
docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/use-the-properties-window
docs.microsoft.com/en-us/office/vba/api/excel.worksheet.codename
@@coloringontheline dude. you are the real MVP here. a year later, helping me out. I used "Sheet3" and it worked for me.
You make a lot of mention of "Protected sheets", assuming you have some control over the actual workbook, you can protect the sheets via VBA and by doing that you have the option of using the UserInterfaceOnly:=True with the Protect method, which allows most VBA activity to run against protected sheets. If I control I workbook, I generally have a "ProtectSheets" macro that runs automatically when the workbook is opened, to apply this setting
Find is a no-go for me because it changes the settings that users use on the front end. When I hit Ctrl f or Ctrl h I expect that the settings from my last search haven't changed. I had a coworker use a vba find with a whole workbook setting. Then I would try to replace on a sheet and it would replace in the workbook. Very frustrating.
Just use
Public Function ResetFind()
Dim r As Range
Set r = ThisWorkbook.Sheets(1).Cells(1, 1)
r.Find What:="", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False
r.Replace What:="", Replacement:="", ReplaceFormat:=False
End Function
What about the speed?
When I tried the "best" (i.e. the "find") solution it didn't work for my last column. My last column was W but it reported U. (V was empty). When I examined it I saw that some of the W cells were merged with X cells. When I un-merged W & X it worked. While I would understand if it had reported W or X, I'm not sure why the find method didn't report either of those columns and stopped at U instead. So this is certainly a drawback to that method.
Hi, i need a help....how can i make range a varible insted of using Range("A1:D4") ...i want A,1,D,4 all must be daynamic variable
kindly support
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. Is there a solution for this problem?
I would like to know why you are using LookIn:=xlFormulas and not LookIn:=xlValues because xlFormulas returns cells containing functions?
I have one doubt, regarding vba macro not working properly when I run on more than 100 rows. Can You plz help me how to resolve it?
wow, I'm 62 and just getting into VBA, not sure I'm getting into and EOL language.
I looked at all your options and thought I should share one I use for selecting report print areas
Is it simpler than your option ?
It will fail with jagged data, correct ?
'Declare variables
Dim startcell As Range
Dim Lastrow As Long
Dim lastcol As Long
Dim sh As Worksheet
'Set objects
Set startcell = Range("B2")
Set WS = Sheets("data")
'Find last row and column with data
Lastrow = WS.Cells(WS.Rows.Count, startcell.Column).End(xlUp).Row
lastcol = WS.Cells(startcell.Row, WS.Columns.Count).End(xlToLeft).Column
'Select the dynamic range
WS.Range(startcell, WS.Cells(Lastrow, lastcol)).Select