Re: Auto Row Height in Merged Cells with pre exisiting text

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dave...I copied the modified version to my worksheet and the row height is
not adjusting at all. Do I need to reference the cell range and if so where
does that go in the code. The cell range is A50:AA142

Thanks,

Gary

"Dave Peterson" wrote:

Maybe you can modify this (modified from a previous tested version--but not
tested this time):

Behind the worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

if intersect(target,me.range("A1:AA175") is nothing then
exit sub
end if

If Target.MergeArea.Cells(1).Address <> Target.Address Then
Call AutoFitMergedCellRowHeight(myActiveCell:=Target)
End If

End Sub

In a general module is a modified version of Jim Rech's code:

Option Explicit
Sub AutoFitMergedCellRowHeight(myActiveCell As Range)
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim OrigMergeArea As Range
Dim CurrCell As Range
Dim myActiveCellWidth As Single, PossNewRowHeight As Single
If myActiveCell.MergeCells Then
Set OrigMergeArea = myActiveCell.MergeArea
With myActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
myActiveCellWidth = myActiveCell.ColumnWidth
For Each CurrCell In OrigMergeArea
MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = myActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub



gwinder wrote:

Thanks Dave...here is the code I'm using.

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

Set r = Range("A1:AA175")
If Not Intersect(Target, r) Is Nothing 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 Sub

Where would I insert the code that would leave the row height alone if it is
larger than the possible new row height.

Thanks

Gary

"Dave Peterson" wrote:

Nope. I don't open unsolicited workbooks.

But I looked at the code that Jim posted:
http://groups.google.com/groups?threadm=e1%241uzL1BHA.1784%40tkmsftngp05

And saw this portion.

.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)

This says to not adjust the rowheight if the current row height is larger than
the possible new row height.

Maybe you made a change that broke this?????

gwinder wrote:

Dave....I copied the macro from Jim Rech I think so I don't really know how
to make the change you're suggesting. If I emialed the worksheet to you
could you take a look at it please?

Thanks,

Gary

gwinder@xxxxxxxxxx

"Dave Peterson" wrote:

Maybe you can change your macro to only increase the rowheight. (If the
rowheight after your routine would be smaller than the existing rowheight, then
don't change it.)



gwinder wrote:

I have applied a macro to automatically adjust the row height when entering
text into merged cells. It works fine when the whole row has no text in it.
However, when I enter text into a blank cell that is in a row that has text
in it, the row height adjusts to the amount of text I enter in the blank
cell, not the height based on how much text is in the cell next to it.

Cells A1:C1 are merged and wrap text. I have entered six lines of text
which has automatically adjusted the row height to fit.

Cells D1:H1 are merged and wrap text. I have entered three lines of text
which now automatically adjusts the row height hiding three lines of text
from A1:C1. How do I get all six lines to to show?

Thanks,

Gary

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

.

Relevant Pages

  • Re: Auto Row Height in Merged Cells with pre exisiting text
    ... The cell range is A50:AA142 ... "Dave Peterson" wrote: ... Dim CurrentRowHeight As Single, MergedCellRgWidth As Single ...
    (microsoft.public.excel.programming)
  • Re: how do I create event-activated macros that check every cell?
    ... Dim CurrentRowHeight As Single, MergedCellRgWidth As Single ... Dim cell As Range ... > - Can I have the macro run automatically for each sheet, ...
    (microsoft.public.excel.programming)
  • Re: Auto fit wrapped and merged cells in entire worksheet
    ... Dim CurrentRowHeight As Single, MergedCellRgWidth As Single ... Dim ActiveCellWidth As Single, PossNewRowHeight As Single ... For Each Cell In UsedRange ...
    (microsoft.public.excel.misc)
  • Autofit of Merged cell when hit enter key
    ... excel moves the selected cell down one row when the enter key is hit ... and the code runs on the next cell versus the original cell. ... Dim MergedCellRgWidth As Single ...
    (microsoft.public.excel.programming)
  • Re: Concatenating various cells
    ... The column containing the floor numbers is column E and the floors ... I attempted to change the variables in your code to fit those cell ... Dim res As String ... & 100 are the row numbers, adjust as necessary) ...
    (microsoft.public.excel.worksheet.functions)