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.

@mikheilchigogidze4224

2 жыл бұрын

Can you please also make Python channel. Sooner the better

@khalidalisawi8037

2 жыл бұрын

pleas how I can download the source in this great video.

@khalidalisawi8037

2 жыл бұрын

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

@thomaswaldmann9264

2 жыл бұрын

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

@fullpowerrolf

2 жыл бұрын

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 KZread channel. I teach VBA, and I always point my students to your website as the best resource there is.

@Excelmacromastery

2 жыл бұрын

Thanks Alexandre.

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.

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

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

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

Great and useful help for those who do heavy programming in VBA-Excel. Thanks Paul.

@Excelmacromastery

2 жыл бұрын

You're welcome

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

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

@Excelmacromastery

2 жыл бұрын

Thanks John

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!)

@Excelmacromastery

2 жыл бұрын

Glad you like it Roger.

@rogerh2694

2 жыл бұрын

Irvine, CA?? Also in OC!

@LuizFeijotheGuitar

2 жыл бұрын

@@Excelmacromastery hi sir Paul, i love Excel, and i starting to learn about VBA, from Where i Begin?

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.

Another great video Paul!! Very informative! Thanks so much 👍🏻😃

@Excelmacromastery

2 жыл бұрын

Thanks Scott.

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.

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

Excelente aula, parabéns!

Really looking forward to this, Paul!

@Excelmacromastery

2 жыл бұрын

I hope you'll like it Jim.

@jimfitch

2 жыл бұрын

@@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!

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!

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

As usual, great choice of topic for a video and good solution for the problem..

this is absolute awesome!! thank you very much!!!!

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

Great video Paul, thanks.

Excellent presentation Paul !

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.

I have been waiting for this longtime.Thank you Mr Paul KELLY

@Excelmacromastery

2 жыл бұрын

You're welcome

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.

@user-qn5by5iv7u

Жыл бұрын

Thanks for your note!

Got the most wanted video at the right time.

Thank you so much! you dont have idea on how much this will help me

Thanks! Very useful video.

Great Video! Thanks! 👍

Wow! excellent video. Instant sub. Thanks!

really it was a great video that I saw. I saw many videos before but it was the best.

CAN'T WAIT

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!

@Excelmacromastery

2 жыл бұрын

Thanks

Excellent video. A nice addition would have been showing people how to turn the returned column number into a letter.

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.

Thanks for sharing.

Thank you so much

Never thought of it using find for that, thanks

@Excelmacromastery

2 жыл бұрын

You're welcome.

I long to see this premiere Greetings from Santa Cruz Bolivi🇧🇴

@Excelmacromastery

2 жыл бұрын

Thanks Jose

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.

@Excelmacromastery

2 жыл бұрын

Glad it was helpful Zach.

Thanks !!

Very useful

Wonderful videos !

@Excelmacromastery

2 жыл бұрын

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

@Excelmacromastery

2 жыл бұрын

Thank you!

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.

@SolidSnake59

2 жыл бұрын

Well I thought about performance as well, but after all this are only 2 searches, so it shouldn't be so bad.

Thanks

Great 💯👍

Paul, please explain also how to get FIRST column, FIRTS row in range. Thanks in advance!

Amazing must watch it again. Are you on linkedin?

tsk you so much :D

Genius!

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!

@Excelmacromastery

2 жыл бұрын

It does work perfect for that:)

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

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.

@Excelmacromastery

2 жыл бұрын

Thanks Martin. Some good ideas there.

@martinstannard2859

2 жыл бұрын

@@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:)

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?

could you please tell what's the screen recording tool you used? it seems perfect.

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)

@ 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..

@Excelmacromastery

2 жыл бұрын

Class Modules is a big topic. I may do more in the future on some of the concepts involved in designing classes.

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...).

Excellent compare and contrast. Do you have advice on how to handle/trap those cases where the range you're testing might be empty?

@patricknicholson5556

4 ай бұрын

NM. Your eh: captures the situation perfectly.

Do your methods for finding the last row and last column work with merged cells or hidden rows/columns?

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.

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!!

@Excelmacromastery

2 жыл бұрын

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

@wayneedmondson1065

2 жыл бұрын

@@Excelmacromastery Awesome! Thanks Paul :))

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.

@Excelmacromastery

2 жыл бұрын

Glad you like it Ed

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".

how to do ctrl+arrow down as loop until last cell found in vba? that would be appreciated

I use shtWhatever.Cells(rows.count,1).end(xlup).row

I can't congratulate you correctly, because my bad english, but I can say: clap, clap, clap. Thanks

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?

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

Would be good to know how autofilter affects any of the methods…

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.

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.

@Excelmacromastery

2 жыл бұрын

Send me an email and I'll send you the code. Paul ExcelMacroMastery

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

@geoffstrickler

2 жыл бұрын

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.

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.

@davescott7680

2 жыл бұрын

I just created a function that resets the settings. Problem solved.

How can I find the last row if there is an expanded array formula in the longest column?

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

Why no speed comparison? That's the biggest thing I care about in such function atm

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

Download link in the e-mail received doesn't seem to work (anymore?).

Could you please explain the meaning of "on error goto eh"?

I am getting a subscript out of range error with the cells.find method

does it work in listbject ?

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.

@davescott7680

2 жыл бұрын

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

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.

@coloringontheline

2 жыл бұрын

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

@coloringontheline

2 жыл бұрын

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 😅

@miless2111sutube

2 жыл бұрын

@@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".

@coloringontheline

2 жыл бұрын

@@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 KZreadr 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

@ryangraham6878

Жыл бұрын

@@coloringontheline dude. you are the real MVP here. a year later, helping me out. I used "Sheet3" and it worked for me.

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?

Can you please make a separate channel for python only?

What about the speed?

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

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