How to adjust row heights depending of cell content?

Question

I have a table of one column and more than 200 rows. I want to adjust the heights of these rows depending on their contents.

For example when a cell has less than 10 characters then the row has to be 25 pixel and if it has more or equal to 10 characters the row has to be 50 pixel.

These pictures may explain what I want to do:

before

to

after

score 1 · Answer 1 · edited Jul 05 '17 at 13:21

This is based upon your unclear question! I also posted a dynamic option

Option Explicit
Sub ChangeRowsColsInPixels()
  Dim r As Range

  For Each r In ActiveSheet.UsedRange

    Dim length As Integer
    length = Len(r.Value)
    If length > 0 Then
        If length < 10 Then     
            r.RowHeight = 25
        End If

        If length >= 10 Then
            r.RowHeight = 50
        End If
    End If

  Next r

End Sub

How do I add VBA in MS Office?

You didn't ask about the alignment of text within a cell but as you can middle align I assume this suffices

You also don't appear to want to take any action if the number of characters is between 10 and 20

Before

After

And with Middle Align

score 0 · Answer 2 · edited Jul 05 '17 at 13:21

As a second solution to my other post, this will offer a more dynamic approach, which is where the height will update based upon the number of characters (this hasn't been properly tested for upper limits/maximums and so with a large long string/value, it's likely to thrown an exception so I've added a hard coded limit which seems to be around the limit in Excel 2010)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim r As Range
  Dim defaultHeight As Integer
  defaultHeight = 25

  Dim maxHeight As Integer
  maxHeight = 399

  For Each r In ActiveSheet.UsedRange

    Dim length As Integer
    length = Len(r.Value)
    If length > 0 Then
        Dim heightToUse As Double
        heightToUse = defaultHeight + length
        If (heightToUse > maxHeight) Then
            r.RowHeight = maxHeight
        Else
            r.RowHeight = heightToUse
        End If
    End If

  Next r

End Sub

Make sure to add it to the specific worksheet as this fires and re-calculates as you enter new values

How do I add VBA in MS Office?

Before

After (with Middle Align in the Alignment tab of the ribbon)

How to adjust row heights depending of cell content?

2 Answers2