using row autofit but with minimum height

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.


--
Richard

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



"Richard" <[email protected]> wrote in message
news:[email protected]...
>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.
>
>
> --
> Richard

Chip,
1. I'm not sure why, but your command
Rng.RowHeight =
> Application.WorksheetFunction.Min(Rng.RowHeight, 30)
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


--
Richard


"Chip Pearson" wrote:

> 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
>
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
> > --
> > Richard
>
>
>

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)

--
Richard


"Chip Pearson" wrote:

> 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
>
>
>
> "Richard" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
> >
> > --
> > Richard
>
>
>

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

"Richard" <[email protected]> wrote in message
news:[email protected]...
> 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)
>
> --
> Richard
>
>
> "Chip Pearson" wrote:
>
>> 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
>>
>>
>>
>> "Richard" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>> >
>> >
>> > --
>> > Richard
>>
>>
>>