# AutoFit an Entire Row Based on Text in a Specific Column Within the Row

#### bonzo

##### Board Regular
I need some VBA code for a situation where I have a row of 15 text cells and I want the auto-resize the entire row based the AutoFit of a particular column cell within the row. To explain in more detail, let's say that out of the 15 columns of text cells in a given row, column cells A-E, G-M, and O all have text such that the cell height is the standard 12.75, column cell N has a cell height of 50.00, and column cell F has a cell height of 25.00. With a normal AutoFit on the entire row, the row height would end up being 50.00. However, for presentation purposes, I want the row height for the entire row to be the row height of column cell F -- which would be 25.00 and would mean that only 25.00 worth of the text in column cell N would be visible. Is there an easy way to specify the entire row height to be AutoFit'ed based on a particular column cell within the row or do I have to write VBA code to manually check the required column cell height of column F and then manually set the row height for the entire row? Any help would be greatly appreciated!

#### bonzo

##### Board Regular
I'm thinking font and font size are missing from your calculations and that's why the height calculations are too high.

maabadi, any suggestions on how to factor in the font (Arial) and font size (8pt) into the final height calculation?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

##### Well-known Member
12pt font is like 1/6", i.e., there are 72 points in an inch. using your font size, you should be able to come up with an algorithm that reasonably approximates the necessary row height.
Use something like this to print a list of font sizes & row heights, and use the results to come up with your conversion formula.

VBA Code:
``````Sub Test()
Dim r As Range
Dim i as Integer

Set r = [a1]
r.EntireRow.AutoFit = True
For i = 1 to 10
MsgBox "Row height = " & r.Height & vbCrLF & _
"Font size = " & r.Font.Size
Debug.Print r.Height & vbTab & r.Font.Size
r.Font.Size = r.Font.Size + 1
Next
End Sub``````

Replies
3
Views
334
Replies
4
Views
236
Replies
1
Views
227
Replies
3
Views
96
Replies
2
Views
120

1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back