For the last 10 years i've been changing my GoTo cell every month. Button "down" in my sheet to go to the last written row. After a decade this little thing was one of my last little annoying case. Thanks for the explaination! Yet another annoying thing solved. Thnx again. (Sry for my English, it isn't my motherlanguage)
Thanks Jon, this was another great video (an oldie but a goodie)!! This video was very helpful in solving my problem. I'm looking forward to the next 2 videos in the series. Keep up the great job!
Gracias genial su explicacion, estuve buscando esta herramienta y al fin en su canal halle la respuesta. No se ingles pero aun asi pude aplicar lo aprendido.Gracias
Hi Jon.. thanks for this tutorial which I've bookmarked and watched several times since first discovering it. I've always used Range.End which works well enough when you know which column or row you want to search. Range.Find is excellent and at first seemed intimidating. However, after a few instances of building it from scratch and using the _underscore method to drop the arguments to a vertical list in the code, it is pretty easy to understand and does not take too long to build. Using Range.Find insures identifying the true last used row or column of a worksheet and also has the added benefit of making me familiar with the Range.Find syntax for other purpose besides just finding the last row or column.. so.. there is an added bonus to learning that method vs. just using Range.End. Thanks again for the insight and examples.. very useful. Thumbs up!
Thank you for the valuable information If u have a column that has a date and if u want to add value when only much that cell on the next column but the same row what vba code do u use???
+Ickxz That's right! I'm glad you found the solution. The Offset property can be used for Rows or Columns. Offset([Rows], [Columns]). So, Offset(1,1) would offset 1 row down and one row to the right. You can also use negative numbers if you want to go up or left. Thanks again!
Hello there. I can find the row below the last processed row on the worksheet. My question is how can I find the last row between the waistline of two columns. (A:D) find me the last row between the columns
Hi. Thanks for your very informative window. I am using the exact code as you used but it is sowing me runtime error. Please guide me how to fix it Run-time error '1004'
apparently the code is limited to be used on the exact Same "Sheet#" the code is written on. I get the exact same error when I use it to count other sheets in the workbook.
Hello sir, maybe you can help me with a rather complex problem, can help me with that. In the worksheet "Menu" I have in cell "E2" I have data from 1 to 3, now I have cell "B4" and "B10" I have an ID reference to names (there are 20), what do I want now do, for example. if E2 = 1, I want to go to worksheet "1" then look at the IdNr then put the data in the right place. It contains date for "B4" = cell "C6", "E6", "E8". for "B10" = cell "C12", "E12", "E14". So with flea cup and a loop, maybe the next formula. if "E2" = 1 then Workbook 1 if B4 = 1 then and B10 = 2 then Hlookup (B4, workbook 1 (A1: CV21), 1) lookup (B10; workbook 1 (A1: CV21), 3.3) C6 = workbook 1 (places in column 3 row 3) for each number in b4) E6 = workbook 1 (places in column 5 row 3) for each number in b4) E8 = workbook 1 (places in column 4 row 3) for each number in b4) if B10 = 2 and B4 = 1 then Hlookup (B10, workbook 1 (A1: CV1), 1) lookup (B10; workbook 1 (A1: CV21), 3.3) C6 = workbook 1 (places in column 8 (is also column 3 of "B10") row 3) for each number in b4) E6 = workbook 1 (places in column 10 (is also column 5 of "B10") row 3) for each number in b4) E8 = workbook 1 (places in column 9 (is also column 4 of "B10") row 3) for each number in b4) So maybe it is useful to work with a loop. I do not know if this formula is correct. It would be useful to (search on both B4 and B10) than put the data in the right place, is this possible?
Nice one Jon. But please what about if you want to find a last but one non zero cell in a range of a column? How can I go about it? last but one non-zero cell.
Thanks William! For that you will probably need to loop through the cells from bottom to top, and evaluate each cell's value to see if the value is greater than zero. Here is some code that should do the trick. For lRow = Range("A1:A100").Rows.Count To 1 Step -1 If Range("A" & lRow).Value > 0 Then MsgBox lRow Exit Sub End If Next lRow Checkout my free training series on macros & VBA to learn more about how to use Loops and write macros. www.excelcampus.com/vba-training-my-first-macro/ Thanks again!
Hi Tanner, great question! Sometimes you can use Ctrl++Shift+End to get to the last cell. Then Ctrl+D to copy down. That only works if you are in the last column to the right. Here is some code that will select the entire column of the current region, even if there are blanks to the left. Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Select
professional instruction at its best easy to understand with no wasted time or words very efficient
For the last 10 years i've been changing my GoTo cell every month. Button "down" in my sheet to go to the last written row. After a decade this little thing was one of my last little annoying case. Thanks for the explaination! Yet another annoying thing solved. Thnx again. (Sry for my English, it isn't my motherlanguage)
Thanks Jon, this was another great video (an oldie but a goodie)!! This video was very helpful in solving my problem. I'm looking forward to the next 2 videos in the series. Keep up the great job!
Love the clarity of explanation 👍
I know its 2021, but this helped, thanks Jon, you da real MVP
Gracias genial su explicacion, estuve buscando esta herramienta y al fin en su canal halle la respuesta. No se ingles pero aun asi pude aplicar lo aprendido.Gracias
Glad to hear that, @oscardulcey ! 😀
Excellent video, incredibly clear. Thank you!
Well explained and professional audio quality! Thanks.
Hi Jon.. thanks for this tutorial which I've bookmarked and watched several times since first discovering it. I've always used Range.End which works well enough when you know which column or row you want to search. Range.Find is excellent and at first seemed intimidating. However, after a few instances of building it from scratch and using the _underscore method to drop the arguments to a vertical list in the code, it is pretty easy to understand and does not take too long to build. Using Range.Find insures identifying the true last used row or column of a worksheet and also has the added benefit of making me familiar with the Range.Find syntax for other purpose besides just finding the last row or column.. so.. there is an added bonus to learning that method vs. just using Range.End. Thanks again for the insight and examples.. very useful. Thumbs up!
Thanks in a million.
Thank you for the valuable information
If u have a column that has a date and if u want to add value when only much that cell on the next column but the same row what vba code do u use???
Cool video and very well explained.
Perfect work
This will work great for me thank you! but can you make it select the box underneath the last non blank row?
+Ickxz I found the solution :)lRow = Cells(Rows.Count, 1).End(xlUp).Offset(1).Select The offset part is what makes it go 1 under
+Ickxz That's right! I'm glad you found the solution. The Offset property can be used for Rows or Columns. Offset([Rows], [Columns]).
So, Offset(1,1) would offset 1 row down and one row to the right. You can also use negative numbers if you want to go up or left.
Thanks again!
clean and clear , great job . thank you
Thank you very much for this valuable informations and for this great video.
Thank you
Hello there. I can find the row below the last processed row on the worksheet. My question is how can I find the last row between the waistline of two columns. (A:D) find me the last row between the columns
Thank u sir it was very nice well explained
Awesome video. Thanks. How can I reference a worksheet other than the default active one?
I just figured it out. By adding Worksheets("worksheetname"). in front of range. :)
Hi. Thanks for your very informative window. I am using the exact code as you used but it is sowing me runtime error. Please guide me how to fix it
Run-time error '1004'
apparently the code is limited to be used on the exact Same "Sheet#" the code is written on. I get the exact same error when I use it to count other sheets in the workbook.
have you found a fix for this btw?
Hello sir, maybe you can help me with a rather complex problem, can help me with that.
In the worksheet "Menu" I have in cell "E2" I have data from 1 to 3, now I have cell "B4" and "B10" I have an ID reference to names (there are 20), what do I want now do, for example. if E2 = 1, I want to go to worksheet "1" then look at the IdNr then put the data in the right place. It contains date for "B4" = cell "C6", "E6", "E8".
for "B10" = cell "C12", "E12", "E14".
So with flea cup and a loop, maybe the next formula.
if "E2" = 1 then
Workbook 1
if B4 = 1 then and B10 = 2 then
Hlookup (B4, workbook 1 (A1: CV21), 1)
lookup (B10; workbook 1 (A1: CV21), 3.3)
C6 = workbook 1 (places in column 3 row 3) for each number in b4)
E6 = workbook 1 (places in column 5 row 3) for each number in b4)
E8 = workbook 1 (places in column 4 row 3) for each number in b4)
if B10 = 2 and B4 = 1 then
Hlookup (B10, workbook 1 (A1: CV1), 1)
lookup (B10; workbook 1 (A1: CV21), 3.3)
C6 = workbook 1 (places in column 8 (is also column 3 of "B10") row 3) for each number in b4)
E6 = workbook 1 (places in column 10 (is also column 5 of "B10") row 3) for each number in b4)
E8 = workbook 1 (places in column 9 (is also column 4 of "B10") row 3) for each number in b4)
So maybe it is useful to work with a loop.
I do not know if this formula is correct.
It would be useful to (search on both B4 and B10) than put the data in the right place, is this possible?
Nice one Jon. But please what about if you want to find a last but one non zero cell in a range of a column? How can I go about it? last but one non-zero cell.
Thanks William! For that you will probably need to loop through the cells from bottom to top, and evaluate each cell's value to see if the value is greater than zero. Here is some code that should do the trick.
For lRow = Range("A1:A100").Rows.Count To 1 Step -1
If Range("A" & lRow).Value > 0 Then
MsgBox lRow
Exit Sub
End If
Next lRow
Checkout my free training series on macros & VBA to learn more about how to use Loops and write macros.
www.excelcampus.com/vba-training-my-first-macro/
Thanks again!
+William Amankwah Hi William, I updated the reply below with some code. Thanks!
thanks Jon with much appreciation
Hey plz tell me how to add column count() in VBA project
Hi, how can I select last row and define that I would like to select only cells in column B,C,D,E and drag down to another?
it can possible to give simple example and simple coding to show the sum or making sheet automate
How can i add value to a row just below the last row in a table?
what if i have got many different tables in the sheet?
Thank you. How does one copy a formula down through a column to the last row (non-blank cell please?
Hi Tanner, great question! Sometimes you can use Ctrl++Shift+End to get to the last cell. Then Ctrl+D to copy down. That only works if you are in the last column to the right. Here is some code that will select the entire column of the current region, even if there are blanks to the left.
Intersect(ActiveCell.CurrentRegion, ActiveCell.EntireColumn).Select
Thank you Jon.
Excel Campus - Jon
How do I find last uncolored row
Mine shows 'invalid use of property' referting to .End
Anyone knows why?
Very well
Please check visibility of your video.
Well explained and professional audio quality! Thanks.