Excel: How To: Neatly format Text cell with "Wrap text"???

Esteemed Forum Participants and Lurkers:
===============================
EXCEL

I print a lot of Excel sheets, and I always have columns of cells with user input ("Comments", etc.). They always vary from 1 or 2 words to a brief paragraph. I have a lot of trouble getting the cells sized properly after I set the font and column widths for the sheet. Some of the text columns are imported from other data bases, so they don't have the "Wrap text" property set when they are pasted into the Excel sheet. We print hundreds of copies of the sheets, so I try to fit as much as possible on a page.

Here is the code I use for one of my sheets ... the font is 8 point arial, and all cells that need "Wrap text" already have it set.

Code:

---

Sub Squish()
Dim ShtArea As Range
Dim nrow As Integer
Dim i As Integer

Set ShtArea = ActualUsedRange(ActiveSheet)  'From previous forum post
nrow = ShtArea.Rows.Count
For i = 3 To nrow                                'Don't mess with 2 header rows
  With ActiveSheet.Rows(i)
    .EntireRow.AutoFit
    'Fit the row height for any number of rows
    .RowHeight = (.RowHeight * 0.9845) - 1.3  'Optimized for Arial 8 pt.
  End With
Next i
' Just Autosize the bottom row
ActiveSheet.Rows(i - 1).EntireRow.AutoFit

End Sub

---

Does anyone have any good efficient general suggestions for how to "Squish" rows to properly fit "Wrap text" cells? Are there any sequences of setting column width, "Wrap text", and font size that work better than others?

Thank you for your gracious suggestions, comments, and assistance.

Not sure if this would work, but you should be able to set a range object to the used height of a specified column. Then set the wraptext property and auto fit height and width... Just a thought.

Code:

---


    Dim sht as worksheet
    Dim rng as Range

    set sht = activeworksheet
    set rng = sht.Range(sht.cells(1,<column>),shtcells(<maxrow>,<column>))
    With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    rng.Rows.AutoFit
    rng.Columns.AutoFit

    set rng = nothing
    set sht = nothing

---

Something like that - possibly.
<column> and <maxrows> you'd have to fill your self, if you know them. If you don't but you do know that the column in question has continuous data from top the bottom you can use

Code:

---

    lngMaxRow = sht.cells(1,<column>).end(xldown).row

---

Or from the bottom up - again only if you know the data is complete ;)

Post up if you are still having a problem.

Thanks, Vince ...

That gives me a slightly different perspective on the WrapText formatting issue. I've played with your code a bit and it has given me a few new ideas, which was what I was looking for! Now we have to see how the quick experiments with a simple sheet translate to the 'Real World'!