Does UsedRange ever shrink back down on its own?

No. Once a cell has held data or formatting, UsedRange remembers it even after the cell is cleared, until the workbook is saved and reopened, or reset with a specific refresh technique.

VBA Find Last Row: End(xlUp) vs CurrentRegion vs UsedRange

Q: Why does End(xlUp) sometimes return the wrong row?

It returns the last non-blank cell in the specific column you started from, going upward. If that column has a blank cell partway through real data — common on ERP exports — it stops there and reports a row far short of the actual last row.

Q: What is the difference between CurrentRegion and UsedRange?

CurrentRegion expands from a single starting cell until it hits a fully blank row or column. UsedRange spans every cell on the sheet that has ever held data or formatting, including stray cells far outside your real data, which often makes it unreliable.

Q: Which method should I default to for ERP exports?

End(xlUp) anchored on a column that is guaranteed never to be blank — typically an ID or reference number column, not a description or notes column that ERPs often leave empty on subtotal rows.

This guide is part of the FinDataPro Excel VBA Workbook Events series.

Three Ways to Find the Last Row

Run all three on the same sheet to see them diverge:

Option Explicit

Sub CompareLastRowMethods()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ERPExport")

    Dim lastRowEndUp As Long
    Dim lastRowRegion As Long
    Dim lastRowUsed As Long

    lastRowEndUp = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowRegion = ws.Range("A1").CurrentRegion.Rows.Count
    lastRowUsed = ws.UsedRange.Rows.Count

    Debug.Print "End(xlUp): "; lastRowEndUp
    Debug.Print "CurrentRegion: "; lastRowRegion
    Debug.Print "UsedRange: "; lastRowUsed
End Sub

On a clean sheet these three numbers agree. On a real ERP export they rarely do.

Download the Example Workbook — Free

The working .xlsm file with a sample ERP export and all three last-row methods already wired up — run it and watch the three results diverge for yourself.

Where the Code Goes

This lives in any standard module — it is a diagnostic sub you run once to decide which method to trust, then hardcode that choice into the real import macro. It pairs directly with Loop Through Files in a Folder, where getting the last row wrong means silently overwriting or skipping imported rows.

Finance Use Case: ERP Exports With Blank Rows

ERP systems routinely export subtotal rows, spacer rows, or a description column left blank on certain line types. End(xlUp) anchored on that description column stops at the first blank it hits and reports a last row far short of the real data — silently truncating everything below it in a macro that assumes the number is correct.

Which Method Actually Breaks

End(xlUp) — reliable only when anchored on a column guaranteed never to be blank, such as an invoice or reference number column.
CurrentRegion — breaks the moment a genuinely blank row exists inside the data, since it stops expanding at the first fully empty row.
UsedRange — often over-reports, since it remembers every cell that ever held data or formatting, including stray formatting far outside the real data block.

For ERP exports specifically, anchor End(xlUp) on the ID column: ws.Cells(ws.Rows.Count, "A").End(xlUp).Row where column A is the invoice or transaction number, never blank on a genuine data row.

Common Mistakes

Anchoring End(xlUp) on a description or notes column that ERPs often leave blank on subtotal or continuation rows.
Assuming UsedRange shrinks back down after clearing cells — it does not, until the workbook is closed and reopened.
Using CurrentRegion on a sheet with any genuinely blank spacer row between data blocks.
Not re-checking the last row after a filter or sort operation, which can change which row is genuinely last.

Once the last row is reliable, see Loop Through Files in a Folder for the import pattern that depends on it, or Array vs Range Performance for what to do once you know how many rows you are working with.

Full reference: Microsoft Learn Range.End method reference.

Frequently Asked Questions

Why does End(xlUp) sometimes return the wrong row?

It stops at the first blank cell going upward in that column — a blank partway through real data reports a row far short of the true last row.

What is the difference between CurrentRegion and UsedRange?

CurrentRegion expands until a fully blank row or column. UsedRange spans every cell that ever held data or formatting, which often over-reports.

Which method should I default to for ERP exports?

End(xlUp) anchored on a column guaranteed never to be blank, such as an ID or reference number column.

Does UsedRange shrink back down on its own?

No — it remembers cleared cells until the workbook is closed and reopened, or explicitly reset.

Conclusion

Of the three methods, only End(xlUp) anchored on a genuinely non-blank column survives real ERP exports. Test all three once with the diagnostic sub above, pick the reliable one for your data, and pair it with Loop Through Files in a Folder for a complete import routine.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.

Discussion

Comments are moderated and appear once approved.