Phew, I am exhausted!! I jumped ahead of the other videos I needed an answer to something so,I did a search for find last... wiseowl and came to this one. great stuff learned heaps. I love how you make this very succinct.
you prolly dont care but if you guys are stoned like me during the covid times you can stream all the new movies on InstaFlixxer. I've been watching with my girlfriend for the last days :)
Thank you, Andrew: I have spend a reasonable amount of time researching VBA tutorials and you are definitely among the bests. Clear instructions and all relevant information is mentioned. In fact, your practical exercises are so complete I comfortably assume the risk of giving you the supreme title. Keep your coolness up and receive my best vibes to your GPS coordinates.
It wasn't kindness; only fair recognition for a job (very) well done. Thank you again for sharing your knowledge with so much professionalism and talent.
Those videos are so good that it worth donating. I usually don't donate at all but this one is an exception. Thank you again for all the great tutorials!
Hi Andrew, thank you very much once again for this valuable video. Which is a very handy and valuable cornerstone in the Excel-VBA technique. From me also, thank you teacher!
what a wonderful explanation.... Hats off to you Andrew. If you are using an active filter, how to find the first & last cell / Col in a filtered list...? It Seems no one have a proper explanation on this.
Thank you so much your effort.Really I love your videos.Please keep going. You used a lot of ways in order to find last row or last column but which way should we use ? thanks again.
Hi , I Have seen Your tutorial , But I have need to select Next Empty Cell Between Two data In A column , Could You Provide the Suggestion . It will help for me .
Hi Andrew, great tutorial as always. I do have a question that might be indirectly related to the topic above. Is there a tutorial made by Wise Owl that shows how to find one and multiple Columns based on their header name and either delete them or change column header value itself (for instance add lower case, proper case etc) or do something to data bellow the header. I do have so many Workbooks containing only one sheet each with specific columns which needs to be deleted, or rectified, but are located always on different column position. For instance, Column "Date of Birth" is on Sheet("X") Column "D" but on Sheet("Y") in Column "F", or Column "Forename" is in Column "B", but on different sheets Column "Forename" is in different column position. Therefore, code should be dynamic, for dynamic range. At the end it would be great to have them rearranged. Would it be possible to shoot that kind of tutorial please? Many thanks for all the effort and great teaching techniques to convey VBA knowledge to us . 👍 Denin.
Hi Denin! You can use the Find method to find values/text in a worksheet. Take a look at this video for the basics th-cam.com/video/_ZVSV9Y7TGw/w-d-xo.html Once you've identified the cell containing the column name you can do whatever you like to it - delete, move, etc. I hope it helps!
Hi Andrew, great to hear from you, thanks for answering my post. Find() it’s brilliant and does the job, I have gone through you video on Find and Find next() several times, keep forgetting the syntax. I thought to use Array() as it can store multiple Column header names, for example for those which I want to delete or amend rather to employ find function for each of the columns. Is there a way to use find function for multiple columns or is Array() a better option? I do ask because I have tried with find function, it works indeed marvellous but my code gets here in size enormously enlarged as for 200 column header names I have to use find. Therefore, I thought to ask if there is maybe a better or faster way with an array for instance or even with find function. Sorry for troubling you with all these questions. Many thanks, Denin.
@@deninsrmic4165 Hi Denin. You can't use Find to look for more than one item at a time. But that doesn't mean that you need to write the same code 200 times. You can store your keywords in an array or even just cells in a worksheet if you prefer. You can then loop through the items of the array or the cells and perform a Find for each item. I hope it helps!
@@WiseOwlTutorials Thanks Andrew, yes indeed, Find() is great for not more one item at the time. At first glance it was peculiar to see more than one Find () function within my code repeating itself. Therefore, i thought to have a look for better options and variations on my code. Array as you said appears to be tremendously useful and I am working on it to conjure your idea using Array () and loop though. Your tutorial on Arrays is amazing, but still need to understand fully how they work on multiple items especially working with strings. Many thanks for your insights regarding my questions and your effort bringing VBA closer to us, definitely best tool I have ever encountered. 👍
You are such a great teacher, but I have a question based on this tutorial. Perhaps you can lead me to the proper video. After finding the next blank row in a data set, how do I paste a row that was previously cut from a different worksheet. I somehow missed something along the way. Thank you! I enjoy your tutorials very much.
Thank you so much Andrew . You have cleared all my doubts. I have a question since I am a newbie just started learning may be a stupid question to ask. How we can select blank rows and blank columns in the data. Actually, I have data in every after blank row and every after blank column. I want to select only those blank cells and change their height and only blank column and change its width. Please advise, you may direct me if you have any videos on this or any suggestions. I hope I have explained my problem properly. Thank again. I appreciate your response.
Another amazing video, thank you :) Here are 2 alternative ways to select the last used row and column without using the Find Method - at least for me they work without bugs, although I must admit your code with the Find method looks much neater :) Sub FindLastUsedCellInLastUsedRow() Application.ScreenUpdating = False Dim Rows_Count As Long Worksheets("Sheet1").UsedRange.Select Rows_Count = Selection.Rows.Count ActiveCell.Offset(Rows_Count - 1, 0).Select Range("XFD" & Selection.Row).Select ActiveCell.End(xlToLeft).Select Application.ScreenUpdating = True End Sub Sub FindLastUsedCellInLastUsedColumn() Application.ScreenUpdating = False Dim Columns_Count As Integer Worksheets("Sheet1").UsedRange.Select Columns_Count = Selection.Columns.Count ActiveCell.Offset(0, Columns_Count - 1).Select Worksheets("Sheet1").Cells(Rows.Count, Selection.Column).Select ActiveCell.End(xlUp).Select Application.ScreenUpdating = True End Sub
Really nice video! But I have a question. How do you apply and automate the "next empty cell" formula in a User Form ? My problem is that the code for the user form works only for one row. It does bring me to next empty cell down but I cannot continue entering data for that next row. Would really appreciate your help
I would like to know the last used row in column A excluding the Empty string. ( I am enter number series based and formula is copied for 1000 rows. but the formula does not have the result for all 10000 rows every time.
Andrew, Here is a suggestion for a perhaps slightly cleaner solution to the challenge of finding the “next empty row” in a table (region) which may have blank values in some cells (14:08 to 17:58)… (No need to fill blank cells) Range("B2").Offset(Range("B2").CurrentRegion.Rows.Count,0).Select Or, Range("B2").Select ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count, 0).Select Best Regards.
When I copy & paste a group of non continuous cells then sort the cells in numbered order it takes into account the blank cells. I cannot use the end(xlup) to find the last row because it counts the blank cells as if it has data. What do I need to do to so that Excel does not recognize the blank cells.
Hi Andrew, I have a question, While autopopulating cells with formulas, I would like to autopopulate form the activecell to the last cell in the column. I cannot use selection.autofill Destination:= range(activecell,range(activecell).end(xldown)). It shows an error, Can you please help Also How to autopopulate multiple colums all at once. I have column A1, B1, C1 with different formulae. I want to select range("a1:c1") and drag them down to autopouplate the cells with each colums respective formulae all the way till the last populated cell in the adjacent columns. Can you help? Also on a side note when are u going to start posting R programming videos??? Thanks
Hi I have one question for you I want select last used 3 columns using exleft Orland xl right method dynamically could please tell me how to do that selection dynamically.....?
Hi! There are lots of ways you could do this! If you want to use the End property you can combine it with Offset to reference the two corners of the range you want to select. So, if your table begins in cell A1: Range( _ Range("A1").End(xlToRight).Offset(0, -2), _ Range("A1").End(xlToRight).End(xlDown) _ ).Select
There is even better option for selecting the last sell: Range("B2").Select ActiveCell.End(xlDown).Select ActiveCell.End(xlDown).Select ActiveCell.End(xlUp).Select ActiveCell.Offset(1, 0).Select or simply: Range("B2").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0).Select
This is the easiest to memorize (at least for me). This is a very good approach for adding new rows of data, all of them populated with no "blanks". The "extra redundant" are necessary if you are appending new data to just 1 row or zero rows existing data rows (check it and you will see how it works ;) )
If makes sure that you go to the last cell of your data set, than to the last cell of the spreadsheet, than to the last populated cell, and finally to the offset cell. It also works with just 1 cell of data or even no data at all!
Well, you need to remember the exact value "B1048576", I still think that my way is easier to remember :) Anyway, I love your videos, they are really helpful!
i have a code to find the last row range("F300000").end(xlup).select Result: it select cell 150038, although Cell does not contain anything i also write a code Range("f21").SpecialCells(xlCellTypeLastCell).Select Result: it select cell Z300000, although all Cell in this row does not contain anything, and i CLear all with all cell in row 300000,Same result
I tried. It works fine for me. Maybe you try with the button "clear all" from the menu (don't know the English name of the button) Make sure to save the workbook to delete the "dirty cells" as he told in the video.
The amount of knowledge put in this video is very huge. Thank you so much, Andrew. I am back to this interesting series of tutorials.
You're very welcome, Hasan! Thank you for the comment and for watching!
Once again, I am amazed by what you can do with Excel and VBA! I find solace in these tutorials. I can't wait for the book to come out! Thanks Andrew!
Very logical demonstration, thank you.
You're very welcome, thank you for watching!
Thanks for your time and effort Andrew....number one VBA instructor
Phew, I am exhausted!! I jumped ahead of the other videos I needed an answer to something so,I did a search for find last... wiseowl and came to this one. great stuff learned heaps. I love how you make this very succinct.
Jo, you're flying through the videos, good work!
watched the series start to end. Didn't thought this series is still continuing. Greate work. Thank you so much!!
Really learnt a lot from all these videos, thank you teacher!
you prolly dont care but if you guys are stoned like me during the covid times you can stream all the new movies on InstaFlixxer. I've been watching with my girlfriend for the last days :)
@Jason Weston yup, been watching on InstaFlixxer for months myself :)
Thank you, Andrew:
I have spend a reasonable amount of time researching VBA tutorials and you are definitely among the bests.
Clear instructions and all relevant information is mentioned.
In fact, your practical exercises are so complete I comfortably assume the risk of giving you the supreme title.
Keep your coolness up and receive my best vibes to your GPS coordinates.
It wasn't kindness; only fair recognition for a job (very) well done.
Thank you again for sharing your knowledge with so much professionalism and talent.
Stunning. Thank you so much for this excellent video. 💯 Likes and 🌟 🌟 🌟 🌟 🌟.
So glad you guys finally provide a way for donation!
Those videos are so good that it worth donating. I usually don't donate at all but this one is an exception. Thank you again for all the great tutorials!
you are really great sir 👍👍👍👍👍
Cool, lots of different methods of finding the last row, thanks Andrew
You explain very very well. Congratulations! Good continuation my friend!
Thank you, Diogo!
Hi Andrew, thank you very much once again for this valuable video. Which is a very handy and valuable cornerstone in the Excel-VBA technique.
From me also, thank you teacher!
First time i've seen this video, and this what im looking for. Really learn a lot i want to master your Arrays tutorial. Thanks Andrew.
Big Thank you. Fantastically clear explanation. Thumbs up!!
Thank you very much.
You're very welcome, thanks for watching!
Thank you so much Andrew !!!
Hi , thanks .. you can also use lastrow = cells(rows.count,1).end(xlup).row and same with column!! thank you
what a wonderful explanation.... Hats off to you Andrew.
If you are using an active filter, how to find the first & last cell / Col in a filtered list...? It Seems no one have a proper explanation on this.
Thank you so much your effort.Really I love your videos.Please keep going.
You used a lot of ways in order to find last row or last column but which way should we use ? thanks again.
Hi , I Have seen Your tutorial , But I have need to select Next Empty Cell Between Two data In A column , Could You Provide the Suggestion . It will help for me .
Excellent video(‘s) !
Hi Andrew, great tutorial as always. I do have a question that might be indirectly related to the topic above. Is there a tutorial made by Wise Owl that shows how to find one and multiple Columns based on their header name and either delete them or change column header value itself (for instance add lower case, proper case etc) or do something to data bellow the header. I do have so many Workbooks containing only one sheet each with specific columns which needs to be deleted, or rectified, but are located always on different column position. For instance, Column "Date of Birth" is on Sheet("X") Column "D" but on Sheet("Y") in Column "F", or Column "Forename" is in Column "B", but on different sheets Column "Forename" is in different column position. Therefore, code should be dynamic, for dynamic range. At the end it would be great to have them rearranged. Would it be possible to shoot that kind of tutorial please? Many thanks for all the effort and great teaching techniques to convey VBA knowledge to us . 👍 Denin.
Hi Denin! You can use the Find method to find values/text in a worksheet. Take a look at this video for the basics th-cam.com/video/_ZVSV9Y7TGw/w-d-xo.html
Once you've identified the cell containing the column name you can do whatever you like to it - delete, move, etc.
I hope it helps!
Hi Andrew, great to hear from you, thanks for answering my post. Find() it’s brilliant and does the job, I have gone through you video on Find and Find next() several times, keep forgetting the syntax. I thought to use Array() as it can store multiple Column header names, for example for those which I want to delete or amend rather to employ find function for each of the columns. Is there a way to use find function for multiple columns or is Array() a better option? I do ask because I have tried with find function, it works indeed marvellous but my code gets here in size enormously enlarged as for 200 column header names I have to use find. Therefore, I thought to ask if there is maybe a better or faster way with an array for instance or even with find function. Sorry for troubling you with all these questions. Many thanks, Denin.
@@deninsrmic4165 Hi Denin. You can't use Find to look for more than one item at a time. But that doesn't mean that you need to write the same code 200 times. You can store your keywords in an array or even just cells in a worksheet if you prefer. You can then loop through the items of the array or the cells and perform a Find for each item.
I hope it helps!
@@WiseOwlTutorials Thanks Andrew, yes indeed, Find() is great for not more one item at the time. At first glance it was peculiar to see more than one Find () function within my code repeating itself. Therefore, i thought to have a look for better options and variations on my code. Array as you said appears to be tremendously useful and I am working on it to conjure your idea using Array () and loop though. Your tutorial on Arrays is amazing, but still need to understand fully how they work on multiple items especially working with strings. Many thanks for your insights regarding my questions and your effort bringing VBA closer to us, definitely best tool I have ever encountered. 👍
You are such a great teacher, but I have a question based on this tutorial. Perhaps you can lead me to the proper video. After finding the next blank row in a data set, how do I paste a row that was previously cut from a different worksheet. I somehow missed something along the way. Thank you! I enjoy your tutorials very much.
Thanks so much. That works!
Thank you so much Andrew . You have cleared all my doubts.
I have a question since I am a newbie just started learning may be a stupid question to ask.
How we can select blank rows and blank columns in the data.
Actually, I have data in every after blank row and every after blank column.
I want to select only those blank cells and change their height and only blank column and change its width.
Please advise, you may direct me if you have any videos on this or any suggestions.
I hope I have explained my problem properly.
Thank again. I appreciate your response.
Another amazing video, thank you :)
Here are 2 alternative ways to select the last used row and column without using the Find Method - at least for me they work without bugs, although I must admit your code with the Find method looks much neater :)
Sub FindLastUsedCellInLastUsedRow()
Application.ScreenUpdating = False
Dim Rows_Count As Long
Worksheets("Sheet1").UsedRange.Select
Rows_Count = Selection.Rows.Count
ActiveCell.Offset(Rows_Count - 1, 0).Select
Range("XFD" & Selection.Row).Select
ActiveCell.End(xlToLeft).Select
Application.ScreenUpdating = True
End Sub
Sub FindLastUsedCellInLastUsedColumn()
Application.ScreenUpdating = False
Dim Columns_Count As Integer
Worksheets("Sheet1").UsedRange.Select
Columns_Count = Selection.Columns.Count
ActiveCell.Offset(0, Columns_Count - 1).Select
Worksheets("Sheet1").Cells(Rows.Count, Selection.Column).Select
ActiveCell.End(xlUp).Select
Application.ScreenUpdating = True
End Sub
Hi Andrew, do you have video of how to copy/transfer random specific data columns between workbooks?
Really nice video! But I have a question. How do you apply and automate the "next empty cell" formula in a User Form ? My problem is that the code for the user form works only for one row. It does bring me to next empty cell down but I cannot continue entering data for that next row. Would really appreciate your help
How could I use the find method but with a named range, starting from the bottom right? Thanks
Hi I have one question why didn't you make even a single video MS Access macro?
thank you
I would like to know the last used row in column A excluding the Empty string. ( I am enter number series based and formula is copied for 1000 rows. but the formula does not have the result for all 10000 rows every time.
Andrew,
Here is a suggestion for a perhaps slightly cleaner solution to the challenge of finding the “next empty row” in a table (region) which may have blank values in some cells (14:08 to 17:58)… (No need to fill blank cells)
Range("B2").Offset(Range("B2").CurrentRegion.Rows.Count,0).Select
Or,
Range("B2").Select
ActiveCell.Offset(ActiveCell.CurrentRegion.Rows.Count, 0).Select
Best Regards.
When I copy & paste a group of non continuous cells then sort the cells in numbered order it takes into account the blank cells. I cannot use the end(xlup) to find the last row because it counts the blank cells as if it has data. What do I need to do to so that Excel does not recognize the blank cells.
Hi Andrew, I have a question,
While autopopulating cells with formulas, I would like to autopopulate form the activecell to the last cell in the column. I cannot use selection.autofill Destination:= range(activecell,range(activecell).end(xldown)). It shows an error, Can you please help
Also How to autopopulate multiple colums all at once. I have column A1, B1, C1 with different formulae. I want to select range("a1:c1") and drag them down to autopouplate the cells with each colums respective formulae all the way till the last populated cell in the adjacent columns.
Can you help?
Also on a side note when are u going to start posting R programming videos???
Thanks
Hi
I have one question for you
I want select last used 3 columns using exleft Orland xl right method dynamically could please tell me how to do that selection dynamically.....?
Hi! There are lots of ways you could do this! If you want to use the End property you can combine it with Offset to reference the two corners of the range you want to select. So, if your table begins in cell A1:
Range( _
Range("A1").End(xlToRight).Offset(0, -2), _
Range("A1").End(xlToRight).End(xlDown) _
).Select
Thanks 🙏 man it's working.
I was learned lot of things by watching your tutorials👨🏫📓.
From India🇮🇳
@@naiduvikas202 Happy to hear that!
There is even better option for selecting the last sell:
Range("B2").Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
or simply:
Range("B2").End(xlDown).End(xlDown).End(xlUp).Offset(1, 0).Select
This is the easiest to memorize (at least for me). This is a very good approach for adding new rows of data, all of them populated with no "blanks". The "extra redundant" are necessary if you are appending new data to just 1 row or zero rows existing data rows (check it and you will see how it works ;) )
If makes sure that you go to the last cell of your data set, than to the last cell of the spreadsheet, than to the last populated cell, and finally to the offset cell. It also works with just 1 cell of data or even no data at all!
Well, you need to remember the exact value "B1048576", I still think that my way is easier to remember :) Anyway, I love your videos, they are really helpful!
i have a code to find the last row
range("F300000").end(xlup).select
Result: it select cell 150038, although Cell does not contain anything
i also write a code
Range("f21").SpecialCells(xlCellTypeLastCell).Select
Result: it select cell Z300000, although all Cell in this row does not contain anything, and i CLear all with all cell in row 300000,Same result
I tried. It works fine for me.
Maybe you try with the button "clear all" from the menu (don't know the English name of the button)
Make sure to save the workbook to delete the "dirty cells" as he told in the video.