auto resizing of cells or rows
 

Hi,
I am looking for some VBA coding that would do the following:
I have an excel document with two spreasheets, A and B. On spreadsheet B I
am calling for the cell contents (mainly text) from spreadsheet A. The
problem is: when the text is updated in sheet A and becomes longer, whatever
was added in A does not show in B until I expand the cell size in B. How can
I make it so the cell size B adjusts automatically so i can see its full
content? The whole "wrap text" feature does not want since the content is
updated from sheet A (as opposed to directly typed in B).
Thank you so much in advance.
Alec

auto resizing of cells or rows
 

You can use the AutoFit method.

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

HTH,
Jennifer

auto resizing of cells or rows
 

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

auto resizing of cells or rows
 

I think the easiest thing to do is follow Jennifer's advice:
Columns("B:B").EntireColumn.AutoFit

and set it up so that anytime Sheet B is Activated it runs the code:
Private Sub Worksheet_Activate()
Columns("B:B").EntireColumn.AutoFit
End Sub

-Ikaabod



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 i
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



--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=53392