Count number of characters in merged cells + adjusting rowheight

[Luc]

Lets say if the merged cell :
Contains more than 50 characters => double rowheight
More than 150 characters => triple rowheight
........

How do i do that?

 

[Guest]

Sub ABCD()
i = Len(ActiveCell.Value)
h = ActiveCell.MergeArea.RowHeight
If i > 150 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 3 * h
ElseIf i > 50 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 2 * h
End If
End Sub

worked for me.

Don't run it twice on the same merged cells.

 

[Luc]

Thanks for your help !!!

What would the code look like if want to do the following :

I run a macro, which also fits the rowheight of the merged cells.
This macro has to check the rowheight of a range of merged cells (if length

75 then,....)

Example below


Range("FaseTekst").Select
For Each Row In Selection
i = Len(ActiveCell.Value)
h = ActiveCell.MergeArea.RowHeight
If i > 150 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 3 * h
ElseIf i > 75 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 2 * h
End If
Next Row

Sorry for the stupid mistakes i've made, i'm really a beginner !!
I think after each row has been fitted with the good height, the active cell
has to go 1 down.

 

[Tom Ogilvy]

Sub ABCDEF()
Range("FaseTekst").Select
Do While Not Intersect(Range("FaseTekst"), ActiveCell) Is Nothing
i = Len(ActiveCell.Value)
h = ActiveCell.MergeArea.RowHeight
If i > 150 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 3 * h
ElseIf i > 75 Then
ActiveCell.MergeArea.EntireRow.RowHeight = 2 * h
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

worked fine for me in xl97, but I can't guarantee for every version, but I
would think it would work. Test it on a copy of your data.