using row autofit but with minimum height

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using wrapped text.

To make sure all the text can be seen, I set the row height using
Selection.Rows.AutoFit

However, for easy reading, I also want to make sure that no cell heigh has a
value less than 30.
 
Try something like

Dim Rng As Range
For Each Rng In Selection.Cells
Rng.RowHeight =
Application.WorksheetFunction.Min(Rng.RowHeight, 30)
Next Rng


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Chip,
1. I'm not sure why, but your command
Rng.RowHeight =
Application.WorksheetFunction.Min(Rng.RowHeight, 30)
Click to expand...
sets the nearly all the cells to 30 (depending on what was in first cell, it
sometimes ended up <30).

2. I created the following subroutine, with cells A1:A4 having variable
length texts, so that some would only dispay fully with cell.height > 25

Dim Rng As Range
Dim Response As Script

Range("a1:a4").Select
Selection.Rows.AutoFit

ans = MsgBox("Continue?", vbYesNo)
For Each Rng In Selection.Cells
Debug.Print Left(Rng, 5); Rng.RowHeight
Rng.RowHeight = Application.WorksheetFunction.Min(Rng.RowHeight, 25)
Next Rng

-the first MsgBox interrupt allowed me to see that the
Selection.Rows.AutoFit did display all the text in all the boxes (like I
expected)
-However, your command set many of the cells back to row.height 25, so some
of the text was hidden
 
Chip,
Don't know why I didn't see this earlier. Easy fix.
You definitely had right idea.
Just replace your command "Min" with "Max",
i.e.
Old: Application.WorksheetFunction.Min(Rng.RowHeight, 30)

New: Application.WorksheetFunction.Max(Rng.RowHeight, 30)
 
Yeah, I had Min where Max was to be used. Sorry for the
confusion.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top