Rating:        Based on 6 ratings
Reviewed:  6 reviews
Downloads: 147299
Released: Jul 6, 2014
Updated: Jul 6, 2015 by AKeeler
Dev status: Stable Help Icon

Recommended Download

Application XSFormatCleaner.xla
application, 44K, uploaded Jul 6, 2015 - 147299 downloads

Release Notes

Modified the commandbar code to use CommandBar IDs instead of English names.

Reviews for this release

The solution for me was contained in the reference noted in #3 (I think), above: https://computertrainer.wordpress.com/2009/07/28/permanently-remove-empty-rowscolumns-at-the-end-of-an-excel-spreadsheet/ That selection basically says, “Highlight the columns or rows that are extra (by using control/shift/end to find the last cell), right click, delete and immediately SAVE.” By Saving the change, Excel then remembers that nothing is in those cells and quits trying to extend the spreadsheet out into forever (in my case, it was Cell AW 21,464, give or take).
by DennisLDunn1 on Aug 29, 2016 at 3:52 PM
Aghast in a good way. Performed exactly as expected - removed the formatting from empty cells, including some sections that are empty until a script is run. What was unexpected was the extent of the file size reduction (140MB to 175kb) and the increased performance of the workbook. Thank you so much!
by aghast on Apr 25, 2015 at 1:22 PM
The code itself is wonderful, indeed. However, in my case it completely stripped my Excel file! All conditional formatting was erased! All cells that were linked now showed #VERW! So, all links were removed. So it does too much cleaning, I guess. But still, thanks for the xla file, though. This much work deserves appreciation, still.
by jpa_rijks on Sep 16, 2014 at 1:42 PM
It did not delete excessive rows but I found a way that did: http://computertrainer.wordpress.com/2009/07/28/permanently-remove-empty-rowscolumns-at-the-end-of-an-excel-spreadsheet/
by ao266 on Mar 11, 2014 at 12:28 PM
I'm not sure what went wrong for CBen, but this marco has been wonderful. First, it tells you that you have to save your document before the changes become permanent. So I'm not sure why you couldn't just "undo" what happened before. I mainly use this when I get excel errors that turn 20 kb sized files int 40 mb sized files for no reason. I have no idea why this error happens, and I can't seem to manually fix it with the clear all command, but this does the trick. I wish I could read the code to see what commands the macro is running, but I am not that advanced. This file works perfectly at reducing formats. I haven't tried it, but I have some excel documents that have been rolled forward year after year and the styles are reaching their limit, I'm hoping this macro can fix that problem too. Only complaint(s) (reason it didn't get five stars): 1. On some large files it times out or takes so long that I can't sit and wait for it to finish. 2. To fix issues with 1, it would be nice if it was possible to add a feature or another macro that only works on selected cells, since this marco goes worksheet to worksheet. Otherwise great tool and thank you!
by Michael95621 on Jan 22, 2014 at 5:32 PM
Used on a large spread sheet that had last cell issues. Hilighted only excess cells. All data and formats removed - will need to dig out the back-up copy now.
by CBen on Feb 24, 2015 at 6:18 PM