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

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,765
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think height of All cells in one row is same, Is it your purpose width of Columns?
Then
VBA Code:
Columns("A:E").ColumnWidth=columns("E").ColumnWidth
Columns("G:M").ColumnWidth=columns("F").ColumnWidth
For Rows Hieght
VBA Code:
Rows("1:8").RowsHeight=Rows("9").RowsHeight
 
Last edited:

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
No, I'm not concerned with column width.

1611415928008.png
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,765
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
What about:
VBA Code:
Range("A1:C1").RowsHeight.Autofit
Rows("1:2").RowsHeight=Rows("3").RowsHeight
Rows("4:6").RowsHeight=Rows("3").RowsHeight
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,765
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Test this:
VBA Code:
Sub RowsHeight()
Dim K As Double, H As Long, W As Long, Val As String
Val = Range("C1").Value
Rows("1").WrapText = False
Rows("1").AutoFit
H = Rows("1").Height
W = Columns("C").Width
K = Len(Val) / (W / 6)
Rows("1").WrapText = True
Rows("1:6").RowHeight = K * H
End Sub
 

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
Test this:
VBA Code:
Sub RowsHeight()
Dim K As Double, H As Long, W As Long, Val As String
Val = Range("C1").Value
Rows("1").WrapText = False
Rows("1").AutoFit
H = Rows("1").Height
W = Columns("C").Width
K = Len(Val) / (W / 6)
Rows("1").WrapText = True
Rows("1:6").RowHeight = K * H
End Sub
I keep getting "Run-time error '1004': Unable to set the RowHeight property of the Range class" for the last line of code.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,765
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Are you change code? I don't Use Range at Last row. I use Row.
If You want Use range:
VBA Code:
Range("A1:C6").EntireRow.RowHeight = K * H
 

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
Are you change code? I don't Use Range at Last row. I use Row.
If You want Use range:
VBA Code:
Range("A1:C6").EntireRow.RowHeight = K * H
Okay, this helps. Because of the subroutine being used that processes one row of data at a time, I had to change "Range(A1:C6)" to "myWorkSheet.Rows(rowNum)". This code block now very is close to doing what I need it do. Thanks for the help so far. The problem now is that the "K * H" calculation ends up being too large of a number. The row height, for example, for one of my rows needs to be somewhere close to 71.25 points, but "K * H" ends up being 149.25 points. H = 16; W = 176; K = 9.3409090909.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,765
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I set based your C column Text length.
First you set column width. then if your column C length is very long change Range C1 to Cell is best fit for your purpose.
 

bonzo

Board Regular
Joined
Oct 23, 2002
Messages
67
I set based your C column Text length.
First you set column width. then if your column C length is very long change Range C1 to Cell is best fit for your purpose.
I'm thinking font and font size are missing from your calculations and that's why the height calculations are too high.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,279
Messages
5,635,265
Members
416,850
Latest member
Sidddharth

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top