Merged cells auto size with VBA code

[ed.ayers315]

Hello forum,

I read many posts on this subject.

I put together the following code and have a worksheet in my Dropbox. C:\Users\EDWARD P AYERS\Dropbox file name is TEXT MERGE RESIZE

This code is not perfect; it does adjust with different column widths and font sizes. It uses formulas on Sheet1 to help get the sizing close. It also has a method with a formula to account for Char (10) additions in the text of the cell.

What I am looking for is help to make this work in the following manner:

1) have a variable range of rows 2:300

2) have variable columns from B:J

3) look at each row, select the value for the largest merged cell and run the code below for that intersecting cell.

4) loop through from row 2 to 300

5) should only work on visible rows.

Sub row_resize()


Dim CurrentFont As Long
Dim CurrentLen As Long
Dim Char10 As Long
Dim RowHeight As Long
Dim CombinedRowHeight As Integer
Dim columnwide As Integer
columnwide = Columns("C:C").ColumnWidth
                     
    RowHeight = Range("h5").Value
    
        CurrentLen = Len(Range("c5"))
            CurrentFont = Range("c5").Font.Size
            Range("$j$5").Value = CurrentFont
            Range("$l$5").Value = columnwide
                CombinedRowHeight = (RowHeight * (CurrentFont / 9))
                    
                    Rows("5:5").Select
                    Selection.RowHeight = CombinedRowHeight
                    


End Sub

 

[jsotola]

what end result are you trying to achieve?

what do you mean by largest merged cell? (overall width in pixels? height? numerical value? number of merged cells?)




note:

your last two lines can be replaced by

Rows("5:5").RowHeight = CombinedRowHeight

no need to select row to set its RowHeight

 

[ed.ayers315]

jsotola,

Thanks for your reply.

I am counting the characters and returns in the merged cell to determine the row height.

With the formulas on sheet1; it adjusts the row height based on those results along with the font size and column width.