Reset of Last Row/Column

[ssanjy]

Hello Everyone,

I'm having a problem relating to resetting last row/column in Excel. I've faced this problems many times and haven't found a proper solution yet.

I've used the Ctrl+End and deleting rows/columns, clearing all formats, and even used Excels built-in add-in (Inquire-Clean Excess Cell Formatting) but they don't work in certain cases. In fact, sometime the excel file size grows instead with no going back.

I've tried some alternatives like copying the contents entirely and pasting in a new worksheet and deleting the old sheet but this is not practical every time since I've links to the cells and I can't re-link them each time the problem appears.

Also, alternatives like saving the file in 97-2003 format or even opening and saving using Excel's alternatives like LibreCalc does the job but it creates doubt that the data which are out of range than the row/column limit (which we might not know exists) will also get removed.

A sample file has been uploaded in http://bit.ly/ExcelProb

Would like your expert advice and solution for the resolution of the problem.

 

[mole999]

excel has to track changes for undo to work, that increases file size, the best compression is with xlsb (binary). ASAP utilities works well as recovery. The only reason that bigger cells occur is due to overslecting so if you have a range of 1000 yet you set 100000, that where the final row occurs and you move back from

 

[ssanjy]

excel has to track changes for undo to work, that increases file size, the best compression is with xlsb (binary). ASAP utilities works well as recovery. The only reason that bigger cells occur is due to overslecting so if you have a range of 1000 yet you set 100000, that where the final row occurs and you move back from

Thank you for your reply. But I was wishing that I could find a solution on how to reset the last row to the last cell with data.

 

[mole999]

Thank you for your reply. But I was wishing that I could find a solution on how to reset the last row to the last cell with data.

First thing is understand why you have an over selection, then a simple search gong back 15 years provides thi as an option https://www.mrexcel.com/forum/excel-questions/75937-simple-vba-code-reset-last-cell.html