For instance, let's suppose that you open a workbook, press Ctrl End, and you are taken to cell F27.
If you then delete three rows and one column, you would expect that Ctrl End would take you to cell E24.
Now there are only 420 cells in the used range, instead of 15.7 million!
If you don’t have a copy of my Excel Tools, you can use the following macro.
It adds a sheet to the active workbook, and lists all the sheets, along with their used range address, and the number of cells in the used range. Address & ", " Next sh str Sh = Left(str Sh, Len(str Sh) - 2) End If With ws Temp .
You can find a version of this tip for the older menu interface of Excel here: Finding the Last-Used Cell in a Macro.
Allen Wyatt With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. Automate virtually any routine task and save yourself hours, days, maybe even weeks. Row This works with columns containing values; from memory, there may be complications in a column containing formulas which can return blank.