VBA · Ranges
UsedRange
Every cell the sheet considers used, including formatted empties.
When to use it
UsedRange is every cell Excel considers used on the sheet, including formatted but empty cells. Good for a quick size check, unreliable as a data boundary.
The code
- Code
ActiveSheet.UsedRange.Rows.Count
Worked examples
Row count
ActiveSheet.UsedRange.Rows.Count → How many rows Excel considers used
Size check.
Address
ActiveSheet.UsedRange.Address → The address, such as $A$1:$H$500
Diagnose bloat.
Reset trick
ActiveSheet.UsedRange ' then save → Referencing it can reset a bloated used range
An old trick.
Worth knowing
- Use End(xlUp) for the true last row of data.
- UsedRange may not start at A1; Rows.Count is not the last row then.
- Ctrl + End in the sheet shows the same corner.
Where it goes wrong
- Formatted empty cells inflate it.
- Its first row is not always 1.
Related
Learn the moves here — or let Wauvel run them on your numbers.
$99/mo, everything included. Free for 14 days, no card.
One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.