AutoFit not adjusting row height in VBA code in Excel 2007

Question

I know many discussions have addressed this but I have not found a solution. I regularly produce worksheets with about 100 records of text and date fields, each of which must be transposed into a 2-column table and printed to a PDF file.

To do this I have been using VBA code which works through the worksheet rows to sequentially: copy/transpose into two columns in a separate worksheet (Template) and then use rng.ExportAsFixedFormat Type:=xlTypePDF to create the PDF file.

It has all been working fine for several years, until someone recently noticed that sometimes the largest field does not show all the text from the Excel cell. It is invariably cut off after about 1000 - 1100 characters.

Many discussions mention that there is a 1024 limit on cell displays, but I thought this only applied to Excel 2003 and before - 2007 should be fine shouldn't it? In any case, I have found it is always possible to manually adjust the Excel field to reveal all of the text (both in the original worksheet and the temporary 2-column Excel table), sometimes totalling more than 2000 characters. Of course, I don't want to manually adjust and print to PDF 100 times on a regular basis. So I used AutoFit: Sheets("Template").Rows("1:18").Selection.EntireRow.AutoFit

Unfortunately this does not seem to duplicate the manual cell expanding that we have tried successfully. None of the cells is merged. All are wrapped and General formatted. I have tried cleaning text entries via Notepad before entry and inserting blank rows with Alt-F (as suggested elsewhere).

If AutoFit will not work, I am thinking maybe I could include some code to set a customised row height for each table by getting the total word count (is there a function?) and setting row height to about 0.8 * number of words - based on initial calculations.

Any ideas please?

Answer 1

I've used a method similar to what you suggested your last paragraph but with a character count instead of a word count. The VBA function is:

len(range("A1").value)

I did it since I had merged cells and they wouldn't autofit.

You'll have to calibrate for your column width, font and font size but from what I've learned there's no exact method. If you set your characters per line factor too high you might cut off a line, too low and you might get a blank line.