Jim Rech's macro won't work (autofitting merged cells)

[Ron M.]

I cut and pasted Jim Rech's macro to autofit rows containing merged
cells into the worksheet code and it has no effect whatsoever. Neither
does putting it in the workbook code. This is Excel 2004 for the Mac,
running OS X. I REALLY need to be able to do this; can anybody help?

Thanks,
Ron M.

 

[JE McGimpsey]

First, there are several versions of "Jim Rech's macro"...

Is the macro an event macro?

Or, if not, did you call it from the Worksheet_Change() event macro?
Simply putting a macro in a worksheet or workbook code module doesn't
make it work automatically.

Some versions of Jim's macros operate on the active cell. If you have
your preferences set to move the active cell when you hit Return, then
the macro will attempt to operate on the incorrect cell. You can change
the macro to refer to a particular cell, or you can modify it to refer
to the changed cell if it's an event macro.

 

[Ron M.]

Thanks JE. Well, I stuck it in the "This Workbook" code like I do
everything else. I'm not clear on what your instructions mean, in any
case.

In this spread sheet, there are about 20 different places where there
are merged cells, and the user needs to be able to enter multiple lines
of text while the row autofits. So I need some kind of "generic" code
that will run regardless of where the text is being entered.

Interesting that some of you whizzes can do this in a couple dozen
lines of code, but Microsoft can't... (-;

Ron M.

 

[JE McGimpsey]

My rule of thumb is to use the ThisWorkbook module only for
Workbook-level event macros, and worksheet modules only for
worksheet-level event macros. Everything else goes in regular code
modules (Insert/Module in the VBE). This is a very common convention.

You may want to look at

http://www.mcgimpsey.com/excel/modules.html

and

http://cpearson.com/excel/codemods.htm

It's not so much that MS *can't* do the autofit as it is that changing
the behavior risks breaking thousands of existing apps.

You might consider a worksheet-level event macro something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells, Range("A1,B2,C3,D4:F6")) Is Nothing Then
AutoFitMergedCellRowHeight Target
End Sub

Then use a version of Jim's macro like (untested):

Sub AutoFitMergedCellRowHeight(Optional rTarget As Range)
Dim CurrentRowHeight As Double
Dim MergedCellRgWidth As Double
Dim CurrCell As Range
Dim TargetCellWidth As Double
Dim PossNewRowHeight As Double
If rTarget Is Nothing Then Set rTarget = ActiveCell
If rTarget.MergeCells Then
With rTarget.MergeArea
If .Rows.Count = 1 And .WrapText = True Then
Application.ScreenUpdating = False
CurrentRowHeight = .RowHeight
TargetCellWidth = rTarget.ColumnWidth
For Each CurrCell In rTarget
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

 

[Ron M.]

JE, would you mind telling me just how to install that? I put it in the
worksheet's code, and it had no effect. How do I change those cells up
in that first section?

Ron M.