Get the real used range of a worksheet

Update 1-Dec-2021: Timing tests show that a binary search method is the fastest.

VBA programmers use many different ways to find the bottom right hand corner of a sheet’s data, the range which encompasses all the data cells but not empty formatted cells or cells which were previously used but are now empty.
Paul Kelly’s good introductory video describes well the problems with four common methods:
.CurrentRegion, .UsedRange, .SpecialCells(xlCellTypeLastCells), and .End(xlUp)
The method most experienced people advocate is to use the .Find method for any data (“*”), starting at A1, and searching backwards, so that the search begins at the bottom of the sheet.
However, that will return only the first cell if the data found is in a merged range.
And it will only search within visible rows, and ignore rows filtered out, which may be the last rows of the sheet.

Another way, posted by Tim Jeffryes on the Excel-L mail list, is to use a formula to find the maximum row and column that contain data. That works well for reasonably sized sheets, but if the used range exceeds 45 million cells (that may depend on your version of Excel), which may happen if people have formatted entire rows or columns, the formula returns #VALUE!.

I provide a sample workbook to test all these scenarios, with a GetLastCell() function. It tries the formula method first, and if that returns an error value, it uses a binary search method of the usedrange, to find the cell at the intersection of the last used row and column. In practice, you may as well use BSearchLastCell() all the time, it’s faster than the formula method.

For a sample data sheet with a data range A1:K1001, the .Find method takes 13ms, the formula methods 30ms, and the binary search only 1.7ms. For a test sheet formatted to XFD1048576 and a data range to K1001, Find takes 103ms, the formulas fail, and the binary search only 3.8ms.

I also provide a sub ShapesLastCell() that reports the bottom right shape. If you want to delete apparently excessive rows or columns, you may want to not delete shapes, or move them to a better position.

The main sub testAll() runs all tests on all sheets. It shows :
1) The Range.Find and MAX(SUBTOTAL(… methods omit the rows hidden by filtering.
2) The ISBLANK and MMULT formulas search hidden rows but are limited to a usedrange of c.45M cells.
3) Shapes can be placed outside the used range.
4) The best method is to use the BSearchLastCell function.
5) Any data cell found needs to be adjusted for any merged range that it is part of.

Let me know what you think – is this useful for you?