Count number of characters in merged cells + adjusting rowheight

  • Thread starter Thread starter Luc
  • Start date Start date
L

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?
 
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.
 
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,....)
Click to expand...
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.
 
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.
 
Back
Top