We are setting up a template for users in our organisation to use, and want
this to happen automatically (ie. we don't want staff to have to adjust row
height themselves).
We have selected the rows (whose height had been previously altered
manually) and we chose Format Cells Alignment - Word Wrap on. We've also
chosen the Format, Row, AutoFit command. However, all the text cannot be
displayed unless we manually fix the row. Any suggestions?
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Cells.EntireRow.AutoFit
Application.EnableEvents = True
End Sub
--
Hope this helps,
MarkN
Rows with Merged cells will not Autofit.
You need VBA code to do that.
Here is code from Greg Wilson.
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
Gord Dibben MS Excel MVP
Gord Dibben MS Excel MVP
This fixed the problem ... I appreciate the assistance.
The template contained merged cells (which I neglected to notice or mention
in my first post - sorry MarkN - but I appreciate the input!).
Cheers
JD2
Further to your advice, we used the following code in a macro to
automatically resize rows:
Although we thought it worked a treat at first, further use reveals that if
we change a cell in column B to something shorter, it resizes to fit that
cell (eg. to a row height for one row of text), but if column A has two rows
worth of text, it hides one of those rows. You then have to manually click
in the cell in column A and then out again to get it to automatically fix it.
As this is an organisational template, we can envisage a lot of people
finding this frustrating. Do you have any suggestions on how we could
overcome this issue by adjusting our code?
Any assistance would be gratefully received.
Regards
JD2
Perhaps Greg or Jim Rech, who has written similar code, can jump in and bail me
out.
Why do you feel compelled to use merged cells in the first place?
These cause no end of problems with many Excel functions like copying, pasting,
sorting, filtering and as you have found, autofitting.
Gord
Thank you for your prompt response - I appreciate your time and effort.
I did not design the workbook, but have been asked to fix the issue (and I'm
certainly not much of a coder either!). The workbook in question has several
sheets and a rather complex layout as it collects Key Performance Indicator
statistics and comments, etc. from each of our 35 business units. Thus the
merged cells have been used for displaying different quantities of
information in a pleasing way (personally I avoid them like the plague too!).
Gord, I have posted a question to the general audience again, so hopefully
one of the people you mentioned will respond to my cry for help.
Thank you again
thx
I don't know if Row>Autofit will react to a change in quantity of data when
the results of a formula.
I can't get it work.
This seems to do the job when calculation takes place.
Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
End Sub
Gord
On Wed, 29 Jul 2009 10:54:03 -0700, Debm <De...@discussions.microsoft.com>
wrote: