autofit

[Guest]

I'm trying to get data to fit an excel row. I tried to use the autofit
method, but it si not working. This is my code:

CoverWs.Range("a1").WrapText = True
CoverWs.Range("a1").EntireRow.AutoFit()

Can someone tell me how to fix it?

Thanks in Advance!
~Elena

 

[Guest]

Do you have merged cells in the row?

if not, it worked OK for me. One thing you can try


Sub AAA()
Set CoverWs = ActiveSheet
With CoverWs.Range("a1")
.WrapText = True
.EntireRow.RowHeight = 409
.EntireRow.AutoFit
End With
End Sub

 

[Guest]

Yes, it is actually a string of merged rows, i was just trying to simplyfy
it... Here's the complete code:

it's in a loop, so "intPrintRow" starts with 5 and then adds one each loop.

Dim strA1 As String = "a" & intPrintRow
Dim strJ1 As String = "j" & intPrintRow
CoverWs.Range(strA1 & ":" & strJ1).Merge()
CoverWs.Range(strA1).Value = strMemo
CoverWs.Range(strA1).Font.Bold = False
CoverWs.Range(strA1).WrapText = True
CoverWs.Range(strA1).EntireRow.AutoFit()

Does the code change for merged cells?

Thanks again,
Elena

 

[Guest]

Autofit doesn't work with merged cells.

You would have to figure out the row height yourself and set it.

Jim Rech posted some sample code previously which you can use as an example:

Jim Rech

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth + _
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub