Excel VBA Tutorial: Find the Last Cell, Row or Column on a Worksheet

// FREE Excel E-book "Record Your First Macro"

// Recommended Excel Courses //

// Recommended Excel Templates //

If you are looking for pre-built, easy to use spreadsheets, check out Simple Sheets.

They have Excel templates covering cash flow planning, invoices, expense reports, personal budgets, and more!

VIDEO TIMINGS (See Below...

——
// EPISODE NOTES

In this tutorial video we look at four ways to find the Last Cell, Row or Column on any Excel worksheet using VBA.

#1 - Range.SpecialCells : Find the last USED cell (note: last used cell may be different from the last non-blank cell... e.g. if you change font or color then Excel treats that as a used cell even if there's no data in it)

#2 - Range.End : Like using CTRL + Arrow (watch out... can be confused by blank cells in your data set)

#3 - Range.Find : Like using CTRL + F to bring up the Find & Replace dialog box to search for "*" asterisk wildcard character... except we start in A1 and go backwards instead of forwards

#4 - Custom Function (UDF) : Create your own User Defined Function to handle multiple situations easily (my UDF does last cell, row or column depending on what you enter into the first parameter)

Note: VBA = Visual Basic for Applications (Excel's built-in scripting language)

——
// VIDEO TIMINGS

0:00 - Introduction

0:53 - Option #1 SpecialCells - introduction
1:17 - Option #1 SpecialCells - VBA code
1:47 - Option #1 SpecialCells - last used cell vs last non-blank cell
3:26 - Option #1 SpecialCells - refresh used range in VBA without saving
3:56 - Recap

4:10 - Option #2 Range.End - introduction
5:00 - Option #2 Range.End - keyboard shortcut equivalent
5:52 - Option #2 Range.End - VBA code
7:22 - Option #2 Range.End - last row and column on GOOD data
7:43 - Option #2 Range.End - last row and column on BAD data

8:41 - Option #3 Range.Find - introduction
9:20 - Option #3 Range.Find - VBA code for last ROW
11:29 - Option #3 Range.Find - run macro for last ROW
11:54 - Option #3 Range.Find - how it works (illustration)
12:34 - Option #3 Range.Find - VBA code for last COLUMN
13:13 - Recap

13:41 - Option #4 User Defined Function - introduction
14:05 - Option #4 User Defined Function - VBA code parameters
14:42 - Option #4 User Defined Function - VBA code part 1 - setting defaults
15:48 - Option #4 User Defined Function - tongue twister!
16:14 - Option #4 User Defined Function - error handler
16:44 - Option #4 User Defined Function - VBA code part 2 - select case
17:02 - Option #4 User Defined Function - VBA code part 3 - ROW
18:04 - Option #4 User Defined Function - VBA code part 4 - COL
18:55 - Option #4 User Defined Function - VBA code part 5 - CELL
20:34 - Option #4 User Defined Function - use in VBA subroutine
21:51 - Option #4 User Defined Function - use in worksheet formulas

23:10 - Recap
23:21 - Question: Have you ever struggled to...?
23:30 - VBA Cheat Sheets!

——
// DOWNLOAD WORKBOOK FOR THIS TUTORIAL

Please follow these instructions to learn from my free sample workbook:

▸ Open the workbook and make sure to enable macros

▸ Click on "Get Started" button to see Overview sheet

▸ Press ALT + F11 to open the VBA Editor

▸ Use F8 to step through the code in a subroutine (one line at a time)

▸ I suggest you adapt this to your own needs... just copy and paste the code, then change the code to work for you

▸ If you have a viewer request, leave me a comment below and I'll try to solve your problem in a new video

——
// REFERENCES

Excel Version: 2016 (used in video)

——
// VISIT MY WEBSITE

For more tutorials on Microsoft Excel and VBA visit my website

Connect with me on Social Media

[abhishekshirsekar]

Hii sir gr8 to see ur video on vba can pls share VBA code for selecting blank and non blank cell in table

[thecrochetingclub]

Please how to merge identical rows which have not the same identical number of repitition?

[DAC_AMD]

Hello!
Can you please tell me what formula to use for reading values (numbers) from the last 10 rows in a column? Periodically is added manually one row in the same column, so the formula would read each time the last 10 rows.
Thank you.

[launchexcel]

If you're totally new to Excel Macros & VBA... get my FREE e-book at this link (plus subscribe to my email newsletter)::


Leave comments with any questions you have...

And click on the LIKE button if you found this video helpful.

Thanks for supporting my channel!

[javisantiso]

Winner of a video, I been tryin to find out about "card counting simulator free" for a while now, and I think this has helped. Have you heard people talk about - Tonvelyn Xiylijah Trick - (just google it ) ? Ive heard some interesting things about it and my m8 got cool results with it.