Unable to correct Excel 2007 document formating errors using VBA

[broadway69]

I'm trying to correct some odd formatting errors which occurred when converting an Excel 2003 document opened in Excel 2007. The workbook in question is a series of paragraphs word wrapped in one column with a set width of 45. Each row is (or rather was) AutoFit so the row height matched the paragraph length perfectly.

The issue I'm trying to correct are random spaces at the end of certain paragraphs of text. Each space appears to be a perfect carriage reuturn and appears in about 30 of the 300 rows. AutoFit does not work with code, double clicking or via Excels interface.

I wrote some code which could be the issue, but it fine with all the other paragraphs and some test ones I've created. I can manually grab and move the width but there are enough legacy documents to justify the creation of this add-in. I'm wondering if the issue is indeed my code or there is something else at play.

I've checked the issue cells for blank space before and after as well. I did notice the Category is set to general instead of text. When I try and convert, it changes everything to ########. If I deleted the contents, change the category and paste in just the values of the text desired, everything appears as ####### as well.

I've played with the vertical alignment, word wrap and other formatting variable. If I create a new workbook, set a column width manually to 45, and paste values only into the cell and select word wrap. It eliminates the space.

Does this mean I have to write a conversion sub to copy over the values and manually read formatting settings from old document and then implement in a new document? I mean... isn't that kind of what Microsoft File Format Converter was supposed to do!?!

Another idea I was exploring was a better way to whitewash the formatting of the cell, making it default in all respects like the cell in a new excel workbook. I'm not certain if range.clear will suffice and how to paste values with code.

Below are my subs.

Thanks in advance for all hep and suggestions.

Please Login or Register  to view this content.