This is my code.. I do something like you !

Sub AdjustRowHeight()
Dim mySheet As String
Dim myRange As Range
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
mySheet = ActiveSheet.Name
myTable = Array("table1_1", "table1_2", "table2_1", "table2_2", "table2_3",
"table3_1", "table3_2")
On Error Resume Next
i = -1
Do Until Not myRange Is Nothing Or i = 7
i = i + 1
Set myRange = Sheets(mySheet).Range(myTable(i))
Loop
If i = 7 Then Exit Sub
myRange.EntireRow.AutoFit = True
For i = 1 To myRange.Rows.Count
myRange.Rows(i).Select
MergedCellRgWidth = 0


'Simulates row height autofit for a merged cell if the active cell is merged.
' includes only 1 row.
'Unlike real autosizing the macro only increases row height
'It does not reduce row height because another
'merged cell on the same row may needed a greater height
'than the active cell.

If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = True 'false
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In ActiveCell.MergeArea 'vs 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
Next i
End Sub
--
Alex St-Pierre


" a écrit :

You can use the AutoFit method.

Columns("B:B").EntireColumn.AutoFit

HTH,
Jennifer