I've researched this for 2 days with no luck. I'm not very good at VBA, but I have a document that has multiple merged rows that I need to autofit. I found a bunch of other code on the net but cannot get anything to work. I know it's not good practice to merge cells, but this document really has no other good way to do it. Can someone please help me learn?

Basically as it is typed below, I can go to Tool/Macro/Macro/Run and the screen flashes for a second, but nothing happens.

I can change the code in Sheet 1 from Worksheet_Change to Worksheet_SelectionChange and I get "Application-defined or Object defined Error". The debugger goes to "ActiveCell.Offset(-1, 0).Select". I don't know what to do from there.

Below is the code I have in sheet 1

Private Sub Worksheet_Change(ByVal Target As Range)
Call AutoFitMergedCellRowHeight
End Sub

Below is the code I have so far in Module 1

''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
ActiveCell.Offset(-1, 0).Select
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

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