Find the Last Row, Column, or Cell in Excel VBA with the Range.End Method (Part 1 of 3)

Learn how to find the last used or non-blank row, cell, or column in a worksheet using VBA. In this video I explain the Range.End method which works well when your data does not contain blank cells.

[whissie]

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)

[alanabt]

professional instruction at its best easy to understand with no wasted time or words very efficient

[scottgaines]

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!

[Krewzie]

I know its 2021, but this helped, thanks Jon, you da real MVP

[michalroesler]

Well explained and professional audio quality! Thanks.

[justinwduff]

Excellent video, incredibly clear. Thank you!

[oscardulcey]

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

[wayneedmondson]

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!

[MohAboAbdo]

Thank you very much for this valuable informations and for this great video.

[beyhanismail]

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

[centugurdag]

clean and clear, great job . thank you

[milliadane]

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

[rakeshkumarmadella]

Thank u sir it was very nice well explained

[Ickxz]

This will work great for me thank you!  but can you make it select the box underneath the last non blank row?

[SAXENA]

it can possible to give simple example and simple coding to show the sum or making sheet automate

[kusumbhoyar]

Hey plz tell me how to add column count() in VBA project

[gerardvaneggermond]

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?

[sfalam]

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'

[grzesiekcieslak]

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?

[esthers]

what if i have got many different tables in the sheet?