Linked Spreadsheet rows

[Guest]

When linking spreadsheets together into a rollup spreadsheet I can not get
the rows to autofit? Can anyone help?
Thank you so much...

 

[Gord Dibben]

Do you have wrap text enabled?

Have you any merged cells in the range? Merged cells won't autofit without VBA
code.


Gord Dibben MS Excel MVP

 

[Guest]

Wrap Text is enabled in all the sheets and although I do not have a merged
cell in the row.... I do have one in that particular column. Can you
advise? I am no good with VBA code...

 

[Gord Dibben]

I would get rid of the merged cells.........nothing but problems.

But for event code you can check this out.

Here is event code from Greg Wilson that runs when you ENTER out of a merged
cell.

Note: wrap text format must be preset on the merged cells.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is event code. Right-click on the sheet tab and "View Code".

Copy/paste the above into that sheet module.


Gord