AutoFit Row height for Merged cells with data exceeding row height limit

[J_Raab]

I've been racking my brain for a long time on this and I'm absolutely stuck. I have a unique excel file with some issues that I just can't figure out.

I have a worksheet, "Form", with drop down user selections, that copies the selection into a second worksheet, "Final" (Final!A22:A25=Form!B3, Final!A26:A29=Form!B4 etc...). "Final" has data sets (merged cells D:F, G:L going 4 rows down at a time starting at row 22) that do a vertical lookup using the data in their corresponding column A to find the matching data in a third worksheet, "Hidden", and moves the data from "Hidden"s D:F & G:L merged data sets to the corresponding "Final" D:F & G:L merged data sets. (sorry if that's confusing but it's been a long day and I can't find a better way to describe it at the moment)

This works fine, everything moves like it's supposed to. The problem is the row height.

Some of the moved data is very high, exceeding the max allowance for row height (409.5), can't be helped its a part of a safety form and the steps need to be present and together. I set the worksheet so the user entered data, and corresponding vertical lookup data, would sit in 4 merged rows (giving me more height allowance) and then tried to find a way to write a vba allowing dynamic adjustment of those corresponding row heights based on the data in them. Essentially the merged 4-rows sets would change in height to fit whatever data was in them, that data being based off what the user selected from "Form". I was never able to write anything that would let me do dynamic height autofit for merged rows and but after checking the internet I was able to find a code that works, but maxes each row height at 102.75 (for a total of 409.5). I figured out that it's doing this since the vba sends the data to a single cell and gets then adjusts the affected row height off that cell (allowing a max total of 409.5). This works if the merged cells data is under the 409.5 max height, but doesn't for my purposes.

So can anyone suggest a way to adjust the below code to allow me to dynamically adjust the combined row heights and let all data be visible but keep the process dynamic with the height being a variable to the looked-up data. Or can you suggest a whole other process that might be simpler? I've got decent skill at vba but I'm self taught and always looking to learn so even if the question gets answered, if anyone can see a better process please post the idea so I can look into it and teach myself. Thanks.

Option Explicit
Public Sub AutoFitAll()
Call AutoFitMergedCells(Range("G22:L25"))


End Sub
Public Sub AutoFitMergedCells(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
With Sheets("Final")
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("Z1").ColumnWidth
.Range("Z1") = Left(.Cells(oRange.Row, oRange.Column).Value, newWidth)
.Range("Z1").WrapText = True
.Columns("Z").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("Z1").ClearContents
.Range("Z1").ColumnWidth = oldZZWidth
End With
End Sub

 

[Worf]

Hello

We can try something within Excel, but would you consider automatically transferring the form data to a Word table?
It should be friendlier for cell height adjustment. Therefore, the choice is:

Method 1 – Only Excel, with merged cells.
Method 2 – spreadsheet data manipulation followed by automatic generation of Word form.

 

[J_Raab]

Worf,

Appreciate the reply but I think I may have found a work around over the weekend. I'll be posting the method later, once I verify that it will actually work.

 

[RobVos]

I know this is old, but I ran into a similar situation recently and looked for a solution as well, without success. But I did come up with something that worked for me. I'll describe what I did:

Everything that is typed is in the top cell (or a single cell is not already merged. This is the large data that will exceed 409.5 row height and thus not all be displayed.
I used Split to break the data into parts based on CHR(10) as the delimiter.
I then loop through the array and put each element in a column on a hidden helper sheet (this column is the same size as my column).
I set that row to autofit. I put that row height in an adjacent cell, and then calculate the number of lines using similar method as you found.
I calculate the total height required based on the total number of lines.
I divide the required height by 409.5 and round up to integer (minimum number of cells needed merged to display the contents).
If this is one, I just autofit the cell.
If this is greater than one, I divide the height required by the number of cells it takes (distribute required height evenly).
With the range that starts in the cell of my initial data and goes down the number of required cells:
Set the row height as calculated and merge them.

I then call a routine to set the borders as I desire.

If interested, I can share the specific code for my application (it is on a different computer). I added remarks to explain most things that are done.