VBA to auto fit row height

[bmcglone]

I found this code on the forum to auto fit the row height using a group of merged cells. It works, but I want to try and modify it.

In this scenario on sheets 1, 2, and 3, D3:D6 are merged and A3:A6 are merged. Both contain variable amounts of text.

The range of merged cells the code looks at is D3:D6. Can make it also look at A3:A6 and use the cells that would give the larger row height?

I also need to automatically adjust the page breaks so they do not fall in a merged cell.

If anyone has a better formatting idea to do what I am doing let me hear it.

I am new to VBA and appreciate any assistance.

Public Sub AutoFitMergedCells()
 
  Dim oRange As Range
  Dim tHeight As Integer
  Dim iPtr As Integer
  Dim oldWidth As Single
  Dim oldZZWidth As Single
  Dim newWidth As Single
  Dim newHeight As Single
  
  Set oRange = Range("D3:D6")
  oldWidth = 0
  For iPtr = 1 To oRange.Columns.Count
    oldWidth = oldWidth + Cells(1, oRange.Column + iPtr - 1).ColumnWidth
  Next iPtr
  oldWidth = Cells(1, oRange.Column).ColumnWidth + Cells(1, oRange.Column + 1).ColumnWidth
  oRange.MergeCells = False
  newWidth = Len(Cells(oRange.Row, oRange.Column).Value)
  oldZZWidth = Range("ZZ1").ColumnWidth
  Range("ZZ1") = Left(Cells(oRange.Row, oRange.Column).Value, newWidth)
  Range("ZZ1").WrapText = True
  Columns("ZZ").ColumnWidth = oldWidth
  Rows("1").EntireRow.AutoFit
  newHeight = Rows("1").RowHeight / oRange.Rows.Count
  Rows(CStr(oRange.Row) & ":" & CStr(oRange.Row + oRange.Rows.Count - 1)).RowHeight = newHeight
  oRange.MergeCells = True
  oRange.WrapText = True
  Range("ZZ1").ClearContents
  Range("ZZ1").ColumnWidth = oldZZWidth
  
End Sub