An annoying feature of Excel is not resetting the the used range. - well, it's meant to be a square around all populated cells.- How can you select the Used Range for this example ? Suppose we delete the last 4 rows, and press CTRL END again.It's frustrating when Excel acts as if the active area of a worksheet is significantly larger than the actual area where you have data.Suddenly your scroll bars move you into uncharted areas, such as column TX or row 5,000.Step1: Press CTRL END - This will select the last populated cell Step2: - Now press CTRL SHIFT HOME ... - the last cell that was populated (but isn't now) is selected.To reset the used range, we need to resort to using VBA.As you can see in the screen shot above, there is nothing visible in cell D8, but it is included in the selected used range.
To select only the filled cells on a worksheet, you can use the Find dialog box.If this code is in the worksheet's macro module, and is run, pressing CTRL END will now go to the correct cell.Download workbook with code on how to reset the used range..This may not be all that reliable as there are exceptions, but this gets you what Excel thinks is the last row. Row Numberof Rows In Selection = selection(selection.cells.count)Numberof Columnsin Selection = selection(selection.cells.count).column 'similar to Last Row calculation above but find the last used cell in a column Last Row = Range("A1"). Row ' Place the curor on the last cell in Column E Cells(Cells. Does not do well when rows were inserted and deleted, for instance. Used Range would provide a more reliable value as Excel will usually then recalculate the lastcell. And that was not the only problem: After several delete operations when trying to check the used range Excel hung.