All you need to do is adjust the macro as follows: This macro always takes you to the proper cell—it works as you would expect Ctrl End to always work.It works because apparently Excel, when it calculates the Count property for the number of rows in the worksheet, always resets the "last cell" indicator.I wanted to check the used ranges, but the sample file had several sheets. I really didn’t want to test each sheet individually, with the Ctrl End shortcut. To save time, I used the Sheet Info tool in my Excel Tools add-in, and it showed that one of the sheets had a used range that ended in the last row! To fix it, I manually deleted all the unused rows, and saved the file.
Now it’s gone, but Excel remembers that it was used once. Count, _ str Sh, _ str LC) 'add hyperlink to sheet name .
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 .
is your source for cost-effective Microsoft Excel training.
This tip (11526) applies to Microsoft Excel 2007, 2010, 2013, and 2016.