You are amazing. Such a clear and comprehensive presentation. Your voice is easy to understand, AND as a hearing-impaired person, I really appreciate the captions. Plus! You've written up the whole thing on your website. This is the best methodology I've found for the task. Well done!
Excellent video! Your presentation of "3 ways to find" is very helpful along with the mention of when to use the various methods. Otherwise, one can can feel lost since there's 10,000 ways to do everything in VBA.
Hi Jon. Your tutorials are awesome. Do you have one that loops through a column of values whose column length may change, find rows in a worksheet that contains one of the values in the column and copy and paste the entire row? Thank for any help.
Very useful. Thanks a lot. Follow-up question please... Based on this / your example, how do modify the code to SELECT the Left-most cell of the Next Empty Row? Based on your example I want to select "Cell A8" - since it is offset by (1,0) from the identified "Last Row: 7". Essentially I want to substitute the last line of code (i.e. MsgBox "Last Row: " & lRow") in your example with the SELECT code instead... To end up in Cell A8. Thx.
Very good, just what I need but for a named range object comprising cells A1 to C7. How would I modify it to search for the next empty cell starting from C7? Thanks.
Hi Wilson, Ctrl+Shift+Down or Ctrl+Shift+End would be the VBA equivalent of the Range.End method. When automating a task with a macro, we often need to find the last cell on the sheet. The most common need to is to paste or append data to the bottom of an existing data range. These techniques to find the last used cell or row on the sheet are very handy for the VBA side. I hope that helps. Let me know if you have any questions. Thanks!
Hi Jon,Great video and website. I was hoping to use this Range.Find method to keep spacing between 2 pivot tables the same, instead of manually doing it everyday. The situation is that I have a document that has to keep the same layout. I have a pivot table starting in B8 that the rows are daily numbers so currently (today is April 11th) it is showing 11 days in 11 rows and will grow daily until it has 30 days in 30 rows. I was thinking that I could use VBA to automatically keep 3 spaces between the end of one table and the beginning of the other. I cant seem to figure out when the month resets, goes from showing 30 days to 1, how to get ride of the rows. Any suggestions would be appreciated! Thanks and keep the posts coming!
Hi Greg, It's find of hard to visualize without seeing your spreadsheet, but if I understand you correctly, you might be able to use Range.Find for this. If the date header row of the first pivot able starts in cell C4, then you could use the Range.Find method to look for a blank cell in C4:AF4 (30 columns). If it finds a blank then you will know that the pivot table ends before 30 columns. You can use Range.Find to only search a specific Range. Here is the code. I changed the What parameter to "" for a blank cell. On Error Resume Next lCol = Range("C4:AF4").Find(What:="", _ After:=Range("C4"), _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 lCol will be 0 if none of the cells are blank. I hope that helps get you started.
hi i have found list in column (A) of name this name is repeated several times, I am looking for a code that I fill cell in column (B) with first found if is empty is only once for second time I fill second empty cell these values by textbox
Hi Kailash, Great question! The SearchFormat is an optional parameter. This means you do not have to specify it. When looking at the screentip for any property or method, the parameters in square brackets [ ] are optional. The screentip for Range.Find looks like the following. .Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection], [MatchCase], [MatchByte], [SearchFormat]) The "What" parameter is NOT in square brackets. This means it is required. All the other parameters are listed in square brackets [ ]. This means the parameters are optional, and you do not have to include them. The SearchFormat is used to search for cells with specific formatting properties (fill color, border, bold, etc.) It's the same as the Format... options in the Find menu in Excel (Ctrl+F, Options >>, Format). In this example we do not need to search formatting to find the last used cell, so I did not include it in the macro. I hope that helps. Let me know if you have questions. Thanks!
Hi Jon! But you told in the video that you set all the parameters because just in case someone used the Search and Find function in Excel, because what is used there, Excel will remember when you run the macro. So if someone searches for the format and after that runs the macro it will search for the format also because Excel remembers it. Or did I understand it wrongly? So, should I add the format to the code or not? By the way: Your Videos are really great. Also your website. I'm thankful for that! Because I'm running around looking for macros here and there and then combine them. Takes a lot of time. Better to learn VBA. :-)
Hi Kailash, Great point! The settings are only remembered for the LookIn, LookAt, SearchOrder, and MatchByte parameters. The default value for the SearchFormat property is False. So if you do not specify it, the Range.Find method will not search the format. The Application.FindFormat property is used to specify the formatting properties that you see in the Find window's Format... menu. You can use FindFormat before Range.Find to set the Format properties, then set SearchFormat to True to search for formatting. If you don't include the SearchFormat property then the formatting will not be searched. I hope that helps. Here is the help documentation are Range.Find which also explains more about the saved settings (parameters). msdn.microsoft.com/en-us/library/office/ff839746.aspx
Hi Shi, Great question! ActiveSheet.UsedRange.Rows. Count is going to return the number of used rows in the worksheet. This might NOT start at row 1. For example, your sheet could have 5 blank rows at the top, and then 10 rows of data. The UsedRange property will return 10 rows, not 15. So it might not tell you the last used row on the sheet, just the total number of used rows from the first used row to the last used row. It can also count cells that are formatted as used cells. So the cells might not contain values or formulas and still be considered used. The method you use depends on what you are looking for. I like Range.Find for most applications because of its versatility. UsedRange can also be useful in certain applications. I hope that helps.
You are amazing. Such a clear and comprehensive presentation. Your voice is easy to understand, AND as a hearing-impaired person, I really appreciate the captions. Plus! You've written up the whole thing on your website. This is the best methodology I've found for the task. Well done!
Excellent video! Your presentation of "3 ways to find" is very helpful along with the mention of when to use the various methods. Otherwise, one can can feel lost since there's 10,000 ways to do everything in VBA.
Very detailed and clear explanation.
This is exactly what I needed! 4 years old but still awesome!!!!
Great and thorough explanations!! Thank you very much! Great series
Excellent explanation. I have not found a better one. Thank YOu.
primazzo1 Thanks! I really appreciate the feedback!
Hi Jon,
Great video. How can I find the next empty row within a table of data?
Thank you sir
For explanation
👍👍👍👍🌹🌹🌹🌹
Hi Jon. Your tutorials are awesome. Do you have one that loops through a column of values whose column length may change, find rows in a worksheet that contains one of the values in the column and copy and paste the entire row? Thank for any help.
You are my VB God. very well explained :)
helped a lot. thank you
Very useful. Thanks a lot. Follow-up question please... Based on this / your example, how do modify the code to SELECT the Left-most cell of the Next Empty Row? Based on your example I want to select "Cell A8" - since it is offset by (1,0) from the identified "Last Row: 7". Essentially I want to substitute the last line of code (i.e. MsgBox "Last Row: " & lRow") in your example with the SELECT code instead... To end up in Cell A8. Thx.
Very good, just what I need but for a named range object comprising cells A1 to C7. How would I modify it to search for the next empty cell starting from C7? Thanks.
Thanks for such a great and clear video!!
Hi Jon, I know this video is old, but maybe you can help me. What VBA add in are you using to autocomplete your code as you write?
Hello GrandMaster, control shift down arrow and across to select a range ??? very useful
Hi Wilson, Ctrl+Shift+Down or Ctrl+Shift+End would be the VBA equivalent of the Range.End method.
When automating a task with a macro, we often need to find the last cell on the sheet. The most common need to is to paste or append data to the bottom of an existing data range. These techniques to find the last used cell or row on the sheet are very handy for the VBA side.
I hope that helps. Let me know if you have any questions. Thanks!
Well done. Great video.
Excellent. Thank you.
Another great tutorial with excellent resources - thank you very much!
Hi Jon,Great video and website. I was hoping to use this Range.Find method to keep spacing between 2 pivot tables the same, instead of manually doing it everyday. The situation is that I have a document that has to keep the same layout. I have a pivot table starting in B8 that the rows are daily numbers so currently (today is April 11th) it is showing 11 days in 11 rows and will grow daily until it has 30 days in 30 rows. I was thinking that I could use VBA to automatically keep 3 spaces between the end of one table and the beginning of the other. I cant seem to figure out when the month resets, goes from showing 30 days to 1, how to get ride of the rows. Any suggestions would be appreciated! Thanks and keep the posts coming!
Hi Greg, It's find of hard to visualize without seeing your spreadsheet, but if I understand you correctly, you might be able to use Range.Find for this.
If the date header row of the first pivot able starts in cell C4, then you could use the Range.Find method to look for a blank cell in C4:AF4 (30 columns). If it finds a blank then you will know that the pivot table ends before 30 columns.
You can use Range.Find to only search a specific Range. Here is the code. I changed the What parameter to "" for a blank cell.
On Error Resume Next
lCol = Range("C4:AF4").Find(What:="", _
After:=Range("C4"), _
LookAt:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
lCol will be 0 if none of the cells are blank. I hope that helps get you started.
is there a way to use this method when you need to find the last non-blank of a spilled array?
Thank you very much!
hi i have found list in column (A) of name this name is repeated several times, I am looking for a code that I fill cell in column (B) with first found if is empty is only once for second time I fill second empty cell these values by textbox
what, if you need to find the next row of the last used row...? any suggestion, please...?
what are my options other than "*", how would I search just for numbers?
Did you miss the "SearchFormat:=False" in your code? The macro recorder shows this.
Hi Kailash, Great question! The SearchFormat is an optional parameter. This means you do not have to specify it. When looking at the screentip for any property or method, the parameters in square brackets [ ] are optional.
The screentip for Range.Find looks like the following.
.Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection], [MatchCase], [MatchByte], [SearchFormat])
The "What" parameter is NOT in square brackets. This means it is required. All the other parameters are listed in square brackets [ ]. This means the parameters are optional, and you do not have to include them.
The SearchFormat is used to search for cells with specific formatting properties (fill color, border, bold, etc.) It's the same as the Format... options in the Find menu in Excel (Ctrl+F, Options >>, Format).
In this example we do not need to search formatting to find the last used cell, so I did not include it in the macro. I hope that helps. Let me know if you have questions. Thanks!
Hi Jon!
But you told in the video that you set all the parameters because just in case someone used the Search and Find function in Excel, because what is used there, Excel will remember when you run the macro.
So if someone searches for the format and after that runs the macro it will search for the format also because Excel remembers it.
Or did I understand it wrongly? So, should I add the format to the code or not?
By the way: Your Videos are really great. Also your website. I'm thankful for that! Because I'm running around looking for macros here and there and then combine them. Takes a lot of time. Better to learn VBA. :-)
Hi Kailash, Great point! The settings are only remembered for the LookIn, LookAt, SearchOrder, and MatchByte parameters. The default value for the SearchFormat property is False. So if you do not specify it, the Range.Find method will not search the format.
The Application.FindFormat property is used to specify the formatting properties that you see in the Find window's Format... menu. You can use FindFormat before Range.Find to set the Format properties, then set SearchFormat to True to search for formatting.
If you don't include the SearchFormat property then the formatting will not be searched. I hope that helps. Here is the help documentation are Range.Find which also explains more about the saved settings (parameters).
msdn.microsoft.com/en-us/library/office/ff839746.aspx
Thanks. Cool.
What is the difference between Range.find and Used.Range.Rows.Count?
Hi Shi,
Great question! ActiveSheet.UsedRange.Rows. Count is going to return the number of used rows in the worksheet. This might NOT start at row 1. For example, your sheet could have 5 blank rows at the top, and then 10 rows of data. The UsedRange property will return 10 rows, not 15.
So it might not tell you the last used row on the sheet, just the total number of used rows from the first used row to the last used row.
It can also count cells that are formatted as used cells. So the cells might not contain values or formulas and still be considered used.
The method you use depends on what you are looking for. I like Range.Find for most applications because of its versatility. UsedRange can also be useful in certain applications.
I hope that helps.
Thanks Jon.