12.11 Excel VBA: Find the Last Row and Column

By Ravindra Bagale Last updated: 6 October 2026

Find the last row in VBA with example — in 30 seconds

What: Cells(Rows.Count, "A").End(xlUp).Row jumps from the bottom of the sheet up to the last filled cell in column A.

Why: Data grows every day. A macro with a fixed 11 misses new rows; a last-row line always finds them.

Tiny example: Header in row 1 plus ten orders means the last row is 11. The next empty row is 12.

First look at the data (before → after):

Find the last row in VBA with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — Values the macro gets

Variable	Value
lastRow	11
nextRow	12
Data rows (lastRow - 1)	10

About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Neha Shah) are made up for this lesson on finding the last row and column.

Before table and formula as text

Before — Column A of the Orders sheet

Row	A
1	Order ID
2	BLK-1001
3	BLK-1002
…	…
11	BLK-1010
12	(empty)

About this data: Demo table only — not real customer or company data. Built to practise finding the last row and column.

Formula / click path

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
nextRow = lastRow + 1

Do it step by step:

In a module, declare Dim lastRow As Long.
Type lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row.
Add Debug.Print lastRow and press Ctrl + G to open the Immediate window.
Run the macro with F5. The Immediate window shows 11.
Use For r = 2 To lastRow in your loops instead of a fixed number.

स्टेप बाय स्टेप करून बघा:

Module मध्ये Dim lastRow As Long declare करा.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row type करा.
Debug.Print lastRow add करा आणि Immediate window उघडायला Ctrl + G दाबा.
F5 ने macro चालवा. Immediate window मध्ये 11 दिसतं.
Loops मध्ये fixed number ऐवजी For r = 2 To lastRow वापरा.

स्टेप बाय स्टेप करके देखें:

Module में Dim lastRow As Long declare करें.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row type करें.
Debug.Print lastRow add करें और Immediate window खोलने के लिए Ctrl + G दबाएँ.
F5 से macro चलाएँ. Immediate window में 11 दिखता है.
Loops में fixed number की जगह For r = 2 To lastRow इस्तेमाल करें.

Data size changes every day, so never hard-code 11 or 5000.

Dim lastRow As Long, lastCol As Long
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row          ' last filled row in column A
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   ' last filled column in row 1

It works like pressing Ctrl + ↑ from the very bottom of column A. Alternatives: ws.Range("A1").CurrentRegion.Rows.Count; for an Excel Table ws.ListObjects("tblOrders").ListRows.Count.

Worked example. Range("A2:A" & lastRow) builds the address text "A2:A4801" when there are 4,800 orders.

Ravindra Bagale's Tip

If you use End(xlDown), it stops at the first empty cell in the middle – and many students' macros process only half the data. Always search from the bottom up (Rows.Count … End(xlUp)), and pick a column that has a value in every row (Order ID).

Ravindra Bagale's Tip – मराठी

End(xlDown) वापरला तर मधे एखादी रिकामी cell असेल तिथे तो थांबतो – आणि बऱ्याच students चा macro अर्धाच data process करतो. नेहमी खालून वरती (Rows.Count … End(xlUp)) शोधा, आणि असा column निवडा ज्यात प्रत्येक row ला value आहे (Order ID).

Ravindra Bagale's Tip – हिंदी

End(xlDown) इस्तेमाल किया तो बीच में कोई खाली cell हो तो वह वहीं रुक जाता है – और बहुत से students का macro आधा ही data process करता है. हमेशा नीचे से ऊपर (Rows.Count … End(xlUp)) ढूँढो, और ऐसा column चुनो जिसमें हर row में value हो (Order ID).

Worked example — finding the last row and column

Why

Rohan Patil in Pune and Oliver Bennett abroad both need a clear, repeatable way to use finding the last row and column on a HomeNest Supplies sheet without breaking totals.

What

Finding The Last Row And Column is the Excel idea this page teaches. We will walk one tiny example end to end.

How

Open a blank sheet. In A1 type a short label for finding the last row and column.
Enter a small made-up value for Rohan Patil using € where money appears.
Add a second value for Oliver Bennett using € so you see mixed currencies stay numeric.
Apply the method from this lesson step by step (menu or formula as shown above).
Check the result still works inside SUM or a simple check cell.

Formula / action

Follow the formula or ribbon steps shown in this lesson (keep values numeric).

Result: The demo cell shows the expected result for HomeNest Supplies's sample row.
Meaning: Once finding the last row and column is correct on one row, copy the same pattern down the column.

Practice tasks (10+)

Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.

1. Rebuild the smallest example of finding the last row and column on a blank sheet for Summit Schools.

Hint: Use only the steps on this page. Keep numbers numeric.

2. Change one input for Priya Deshmukh and confirm the result updates.

Hint: If the result is stuck, check absolute vs relative references.

3. Repeat the same steps with a £ or € value for Emma Clarke.

Hint: Formatting must not break SUM.

4. Write the result meaning in one plain sentence next to the cell.

Hint: Imagine explaining it to a new joiners.

5. Add a second row and fill the method down.

Hint: Double-click the fill handle if the pattern is consistent.

6. Break the example on purpose, then fix it using this lesson.

Hint: Common break: text instead of a number.

7. Apply finding the last row and column to a 5-row made-up Summit Schools table.

Hint: Keep column headers clear.

8. Check your answer with a simple total or COUNT.

Hint: A wrong total usually means a format or key mismatch.

9. Swap ₹ and $ on one column and re-check totals.

Hint: Stored values stay the same; only the display changes.

10. List two mistakes beginners make with finding the last row and column.

Hint: Look back at the tip box on this page.

11. Teach the steps out loud in under one minute.

Hint: Why → What → How.

12. Save the sheet as SummitSchoo_practice.xlsx.

Hint: Use a clear file name for class.

Loading practice tasks...

Last used row (Easy): Which row number is the last filled row? The headings are in row 1.
Late orders (Easy): How many rows have Status "Late"?
Total amount (Easy): Add up the Amount column.
Worst delay (Easy): What is the largest number in the Days late column?
Late orders in Pune (Medium): How many orders are Late and in City "Pune"?
Customer for an order (Medium): Find the Customer for Order ID "DL-704".
Money stuck in late orders (Medium): Add up the Amount column for rows where Status is "Late".
Big orders (Medium): How many rows have an Amount of 2000 or more?
Message text (Medium): Build the text "N late orders", where N is the count of Late rows.
Average delay of late orders (Hard): What is the average Days late for rows with Status "Late"? Round to 1 decimal.
Tag for the first row (Hard): For the first row, return "Chase" if Days late is 3 or more, else "OK".
Warning or all clear (Hard): Return "Warning" if any order is 3 or more days late, else "All clear".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Finding The Last Row And Column? — Card 1: What is Finding The Last Row And Column? · Download PNG

Revision card 2: Tiny example — Finding The Last Row And Column — Card 2: Tiny example — Finding The Last Row And Column · Download PNG

Revision card 3: Checklist — Finding The Last Row And Column — Card 3: Checklist — Finding The Last Row And Column · Download PNG

More worked examples: VBA last row and last column

Suppose we are at FoodLane. Every day new orders are pasted below yesterday's orders in column A. Our macro must find where the data ends, so it can add the next day's rows or total them. The number of rows changes every day, so we never type a fixed row number.

Example 1 (simplest): last row in column A

Data is in A1:A120.

Steps:

Press Alt+F11 and insert a Module.
Paste the code below and press F5.

Output: A message shows 120.

Sub LastRowA()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox lastRow
End Sub

Example 2: last column in row 1

Headers are in A1:F1.

Steps:

Paste this in the same module and press F5.

Output: A message shows 6, because F is the 6th column.

Sub LastColRow1()
    Dim lastCol As Long
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    MsgBox lastCol
End Sub

Example 3: last row with data in any column

Some columns are longer than column A.

Steps:

Use Find, which searches the whole sheet from the bottom.
Paste and run the code below.

Output: The message shows the last row that has data in any column.

Sub LastRowAny()
    Dim r As Range
    Set r = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not r Is Nothing Then MsgBox r.Row
End Sub

Method	Best for	Watch out
End(xlUp)	One column	Looks at one column only
End(xlToLeft)	One row	Looks at one row only
Find "*"	Whole sheet	Returns Nothing on an empty sheet
UsedRange	Quick check	Can include cleared formatted cells

About this data: Made-up rows so you can copy the pattern for finding the last row and column. Currencies may use ₹, £, € or $.

Frequently asked questions

How do I find the last row in Excel VBA?

Use lastRow = Cells(Rows.Count, "A").End(xlUp).Row. It starts at the bottom of column A and moves up to the last cell with data.

How do I find the last column in VBA?

Use Cells(1, Columns.Count).End(xlToLeft).Column for row 1.

Why use Long and not Integer for the row?

Excel has more than 1 million rows, and Integer stops at 32,767. Long holds the bigger numbers.