Free Microsoft Excel 2013 Quick Reference

Autofitmergedcellrowheight jim rech Results

Greetings,
Jim Rech's code works great for this, see:
http://groups.google.com/groups?thre...%40tkmsftngp05

But is it possible to trigger this with an event so that when the selection
is changed FROM the merged cell the code runs for the merged cell (ie would
be equivalent to BeforeSelectionChange)
Many Thanks

Hello,

I've attempted to utilize the frequently posted macro that Jim Rech wrote to
assist in this problem that all of us seem to run into at one point or
another. However, I keep getting a compile syntax error directing me to the
13th line of the macro.(MergedCellRgWidth = CurrCell.ColumnWidth +)

I have inserted this macro as a Module. Do I need to select the rows I need
done? Can anyone assist and where I may be going wrong?

Here's the full Module as I've inserted it:

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range, RangeWidth As Single
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
RangeWidth = .Width
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
While .Cells(1).Width < RangeWidth
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
Wend
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

I'm so thankful for Jim Rech's code* (and those who directed me to it) that allows auto-expansion of merged cells, but I have a shared workbook with at least 10 sheets, each of which has more than 40 different merged cells that may need auto-expansion. Because there are so many merged cells, not to mention Excel-phobic users typing into them, I want to avoid personally running this macro for each cell that needs expansion. Unfortunately, my very rudimentary VBA skills are of no help.

Given that, I have several questions:
Can I have the macro run automatically for each sheet, checking all the merged cells (or simply all cells), when the user saves or exits?
Can I have the macro check every single cell in the workbook? I'd like to run this version manually.
Can the macro activation event be when the user has finished entering text and moves to a new cell whether bytabbing,hitting enter, orclicking on the next cell they want to modify?If so, does anyone know how expensive this would be in terms of time and memory? All my users are accessing the workbook remotely and already have a good deal of lag due to outdated equipment.
*Jim Rech's AutoFitMergedCellRowHeight Code

Hello,

I've attempted to utilize the frequently posted macro that Jim Rech wrote to
assist in this problem that all of us seem to run into at one point or
another. However, I keep getting a compile syntax error directing me to the
13th line of the macro.(MergedCellRgWidth = CurrCell.ColumnWidth +)

I have inserted this macro as a Module. Do I need to select the rows I need
done? Can anyone assist and where I may be going wrong?

Here's the full Module as I've inserted it:

Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range, RangeWidth As Single
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
RangeWidth = .Width
For Each CurrCell In Selection
MergedCellRgWidth = CurrCell.ColumnWidth +
MergedCellRgWidth
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
While .Cells(1).Width < RangeWidth
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth + 0.5
Wend
.Cells(1).ColumnWidth = .Cells(1).ColumnWidth - 0.5
.EntireRow.AutoFit
PossNewRowHeight = .RowHeight
.Cells(1).ColumnWidth = ActiveCellWidth
.MergeCells = True
.RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
CurrentRowHeight, PossNewRowHeight)
End If
End With
End If
End Sub

Hello, Thanks in advance for your help.

Before I even ask my question, I am far from being a programer and don't
really even know enough to be dangerous. I am developing a template for a
group of 40 sales reps which will be used to track large accounts. The
template requires the use of merged cells which will need to be able to use
word wrap. I followed previous advice and use the following code from Jim
Rech:
''Simulates row height autofit for a merged cell if the active cell..
'' is merged.
'' has Wrap Text set.
'' includes only 1 row.
''Unlike real autosizing the macro only increases row height
'' (if needed). It does not reduce row height because another
'' merged cell on the same row may needed a greater height
'' than the active cell.
Sub AutoFitMergedCellRowHeight()
Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
Dim CurrCell As Range
Dim ActiveCellWidth As Single, PossNewRowHeight As Single
If ActiveCell.MergeCells Then
With ActiveCell.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
ActiveCellWidth = ActiveCell.ColumnWidth
For Each CurrCell In 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
End Sub

This works fine, just need it to be automatic. So I added this to the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
AutoFitMergedCellRowHeight
End Sub

This mostly works. When I enter text in a merged cell which will require
wrap then press enter, it does not automatically wrap. But when I go back
and click on the cell, it wraps. What do I need to do so I don't have to go
back and click on the cell?

Thanks,
Steve