Autosize cells as data is entered

Hello.

I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is
set to General.

When I add text, it wraps, however, the cell-size is not changing! What do
I have to do to get the cell (or row height) to grow as text is added?

--
Carol

I'm betting you have some "merged cells" which won't Autofit without VBA code.

If this is the case and you're interested in the code, post back.


Gord Dibben MS Excel MVP

On Thu, 22 Feb 2007 10:49:13 -0800, Carol
wrote:

Hello.

I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is
set to General.

When I add text, it wraps, however, the cell-size is not changing! What do
I have to do to get the cell (or row height) to grow as text is added?

Nevermind! Just realized my cells are merged - therefore, none of the
solutions I was trying will work.
--
Carol


"Carol" wrote:

Hello.

I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is
set to General.

When I add text, it wraps, however, the cell-size is not changing! What do
I have to do to get the cell (or row height) to grow as text is added?

--
Carol

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!
--
Carol


"Gord Dibben" wrote:

I'm betting you have some "merged cells" which won't Autofit without VBA code.

If this is the case and you're interested in the code, post back.


Gord Dibben MS Excel MVP

On Thu, 22 Feb 2007 10:49:13 -0800, Carol
wrote:

Hello.

I have my cells set to Wrap & the row is set to Auto-Fit. The "Number" is
set to General.

When I add text, it wraps, however, the cell-size is not changing! What do
I have to do to get the cell (or row height) to grow as text is added?

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!

You (and Greg Wilson)...... BRILLIANT! Worked beautifully!

Thank you for help - and perfect instruction!
--
Carol


"Gord Dibben" wrote:

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!

Hey Gord,

I have a similar problem to the one you addressed in your post. When I
highlight all the rows then select 'format', 'AutoFit Row Height' it will
make the adjustments. However, once new text is added (actually, eveything
in this tab is being controlled by a formula) the rows do not change height
to accomodate the new text. I do have a couple merged cells, but they are at
the very top and are not part of the rows that I am trying to use AutoFit on.
I inserted the VBA code you pasted just in case I have merged cells, but
that didn't help. This is the only other unique thing I can think of that
may be causing it: in the cells of one column I have the values of multiple
texts being returned on separate lines (e.g. one of the cells has the formula
K14&CHAR(10)&L14&CHAR(10)&M14&CHAR(10)&N14).

Can you think of any reason why AutoFit wont work?

"Gord Dibben" wrote:

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!

Even without merged cells Excel doen't seem to like too many linefeeds and will
not autofit after a certain point.

I tried your example formula in A14 with row set to Autofit.

Entry of text in K14 and L14 autofit but entries in M14 and N14 did not increase
row height.

I don't know of a workaround.


Gord Dibben MS Excel MVP

On Mon, 14 Jul 2008 14:29:01 -0700, DoubleZ
wrote:

Hey Gord,

I have a similar problem to the one you addressed in your post. When I
highlight all the rows then select 'format', 'AutoFit Row Height' it will
make the adjustments. However, once new text is added (actually, eveything
in this tab is being controlled by a formula) the rows do not change height
to accomodate the new text. I do have a couple merged cells, but they are at
the very top and are not part of the rows that I am trying to use AutoFit on.
I inserted the VBA code you pasted just in case I have merged cells, but
that didn't help. This is the only other unique thing I can think of that
may be causing it: in the cells of one column I have the values of multiple
texts being returned on separate lines (e.g. one of the cells has the formula
K14&CHAR(10)&L14&CHAR(10)&M14&CHAR(10)&N14).

Can you think of any reason why AutoFit wont work?

"Gord Dibben" wrote:

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!

Does anyone know how to modify this code so that it will work in a protected
sheet?

"Gord Dibben" wrote:

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!

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
On Error GoTo endit
Application.EnableEvents = False
With Target
If .MergeCells And .WrapText Then
ActiveSheet.Unprotect Password:="justme"
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
endit:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub


Gord

On Wed, 15 Oct 2008 16:35:03 -0700, Brian
wrote:

Does anyone know how to modify this code so that it will work in a protected
sheet?

"Gord Dibben" wrote:

I avoid meged cells like the plague simply due to the many problems caused by
these.

But............................

Here is event 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

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

Copy/paste the above into that sheet module.

As you enter data the rows will autofit.


Gord

On Thu, 22 Feb 2007 11:42:45 -0800, Carol
wrote:

It is the case! I think we were both responding to my post at the same
time.. I would love the VBA code - though a novice at that. HOWEVER - if
it's easier than completely re-doing my form, I'll be happy to take a stab at
it!

Thank you Gord!