Need Help on Auto Formating Row Heights 2

[JTBorton]

I am working on a project that will take an array of information from a user form, write it onto a new spreadsheet and format it depending on the data. Some of the cells may have very long text strings. The column width is fixed and I want to row height to vary, with wrap around text on.

I tried the following
With Sheets(shtName)
(The code formats the sheet depending on the data)
.rows(V).AutoFit
End with

How ever the row is not auto-sized to the height of the cell with the longest string of Data. It instead cuts it off and sizes the rows to the standard size that they start at when you create a new sheet. The only other thing I can think of is to set different heights based on the size of the string, such as

Select Case len(string)
Case > 25
rows(v).RowHeight = #
Case > 15
rows(v).rowheight = #
.
.
.
End Select

Any ideas?

 

[SkipVought]

Hi,

Do you have MERGED CELLS?

AutoFit does not do well with merged cells.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Also ensure that Wrap Text is enabled.

With Cells
    .WrapText = True
    .EntireRow.AutoFit
End With

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JTBorton]

Wrap text is enabled. And yes I have merged cells. Is there a way to work around that or do I need to unmerge them for it to work properly?

 

[SkipVought]

"AutoFit does not do well with merged cells."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Actually, merged cells works at odds with many spreadsheet-related features of Excel, and I would not recommend using in most cases.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

If you like the look of merged cells, you can use Center Across Selection instead. 'Gives you the look without the headaches.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mscallisto]

I has a problem formatting column widths when some rows had merged cells. I found that if I formatted the column widths before merging the cells it seemed to work.

sam

 

[JTBorton]

mscallisto

I tried autofitting before merging the cells and it worked - until I merged the cells. Then it squashed them again.

anotherhiggins

How do I use the center across selection property?

 

[SkipVought]

Select the cells that you want to center on, LEFT TO RIGHT.

Format > Cells > Allignment Tab: Horizontal select Center across selection

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Oh yes thats one way. But my question is how do you code it? I want the program to automatically format the cells as it adds information, and only format cells with information.

like
.Range("a4.h3").HorizontalAlignment = xlCenter

I tried
.Range("a4.h3").HorizontalAlignment = xlCenterAcrossSelection
but that didn't work

I wish I could find a big table with all the properties and their parameters in VBA along with a short description of what they do. Microsoft Visual Basic Help is rather limited in its information.

I really appreciate all the help

 

[SkipVought]

.Range("a4.h3") is incorrect syntax.

.Range("a3:h4")

both the ROW precidence and the DOT were incorrect.

Use your macro recorder to get better code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}