AutoFit rows to fit a specific cell in the row

[RogerC]

I always want to show the full text contained in column G. The text in column N will always be much longer than that in any of the other cells in the same row, but I do not want the rows to expand to fit the text in N. The text in column G is my only concern.

AutoFit will make the row much longer than what I need because of this one column with extra long text.

Is there a method to apply AutoFit to rows so that a specific column within the row is always the actual target for sizing the row?

 

[goesr]

Hi RogerC - I found the code below online at: LINK

I tried it on a very small sample and it seems to work. Some tweaking will certainly be necessary, but you can see what was done. Thanks to Andy Pope (OzMVP (Roobarb).

Good Luck

Sub RogerC_autofit()
     
    Dim rngRow As Range
    Dim rngAutofit As Range
    Dim rngCopy As Range
     
    Set rngAutofit = Range("A21")
    For Each rngRow In Range("A1:A20").Rows
        rngRow.EntireRow.SpecialCells(xlCellTypeVisible).Copy rngAutofit
        rngAutofit.EntireRow.AutoFit
        rngRow.RowHeight = rngAutofit.RowHeight
    Next
    rngAutofit.EntireRow.Clear
     
End Sub

 

[goesr]

Hi again RogerC - Please note that for this to work you would need to HIDE the offending columns (N) and then unhide them at the end of the macro, but that would not be too difficult. Good luck.

 

[RogerC]

Hi goesr - Unfortunately, the code above is giving me the following error:

Run-time error '1004': Unable to set the RowHeight property of the Range class.
​

When it reaches this line:

rngRow.RowHeight = rngAutofit.RowHeight
​

Also, I don't understand the significance of the range values "A21" and "A1:A20" in this code, as they really have no immediately apparent relevance to my data range (my data is currently in cell B3:Q120 and will grow downward over time).

 

[RogerC]

As I unsuccessfully attempted to apply goesr's solution (adapted from Andy Pope), I realized what I really need is for my row heights to auto-fit to whichever column contains the longest text within the range of columns E thru H. (The cells are text-wrapped).

Of course, normally, as the cells in each row are initially populated I can just set the row height to show the longest text, the problem is the sheet will get sorted frequently and sorting causes the row-heights to change, not keeping their correctly set height.

Can this code be adapted to cause rows to always adjust to whatever height is needed to show the longest text in cells E through H, keeping in mind I don't want the row height set to longer text that might or might not be in a cell outside that range within the same row?