How to adjust row heights depending of cell content?

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

after

zarqos.os

Posted 2017-07-04T11:58:50.433

Reputation: 25

Conditional formatting can't change row height, your only option here is to use a macro. However I don't understand why would you need such a formatting. – Máté Juhász – 2017-07-04T12:04:23.290

My real table is a bill containing description of items, when the description is too long it will be wrapped automatically but the cell doesn't show all the text, and I don't want to use autofit rows because I want to use my own values of their heights. (sorry of my bad english) – zarqos.os – 2017-07-04T12:14:54.027

Your question isn't actually clear because telecommunications appears to be in a higher cell but doens't have more than 20 characters... – Dave – 2017-07-04T12:50:27.217

I edited the question and the second statement is more than 10 caracters :) – zarqos.os – 2017-07-04T13:14:20.820

two statements : >=10 or <10 :) – zarqos.os – 2017-07-04T13:39:42.877

Answers

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

Dave

Posted 2017-07-04T11:58:50.433

Reputation: 24 199

It works well but is there a way that this macro to be executed automatically once I hit enter the macro takes effect ? by the way I etited my question because I made an error about the second statement. – zarqos.os – 2017-07-04T13:29:40.983

Yes, I've even added a link on how to add a macro which shows this – Dave – 2017-07-04T13:32:31.107

I mean when I input data in the cell and once I hit entre to validate this data the macro will be executed automatically. – zarqos.os – 2017-07-04T13:37:53.910

Look at my second post, it shows what you want with the sub worksheet_change – Dave – 2017-07-04T13:57:31.500

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)

Dave

Posted 2017-07-04T11:58:50.433

Reputation: 24 199

I tried this code and it works, but I note that the height is variable depends on the text length entered while I want it take two values : 25 and 50 pixels. – zarqos.os – 2017-07-04T14:14:19.547

erm... Yes. Are you not able to understand I have given 2 answers? This is a more dynamic approach as it's potentially useful to others. It also includes additional information such as updating on the fly (well, when the cell value changes). I'm sure you can use the bits from both posts? – Dave – 2017-07-04T14:34:01.083