If text length <65 then rowheight = 14

[nitrammada]

Hi Friends,
Does anyone know the VBA to see if the length of a string within a cell is <65. If yes, then make the row height 14, Else wrap the text and add a space of +2?
The +2 is to give a little white space for clarity of reading before the next cell / line of text starts.
I'm wanting to test every cell in column L of the active sheet.
Any help would be appreciated.
Regards
Adam

 

[nitrammada]

@nitrammada, You didn't have success with the latest code I provided in post #7?

Hi JohnnyL, apologies for late reply, have been on holiday. Yes, your post in #7 did work thank you, but it wasn't able to add the space on after the text, that was what I was meaning by the +2. I was hoping to perhaps amalgamate yours and Fluff's code to get the desired result. Any suggestions?

 

[nitrammada]

Ok, how about

Sub nitrammada()
   Dim Cl As Range
  
   For Each Cl In Range("L6", Range("L" & Rows.Count).End(xlUp))
      If Len(Cl.Value) < 65 Then
         Cl.RowHeight = 14
      Else
         Cl.WrapText = True
         Cl.EntireRow.AutoFit
         Cl.RowHeight = Cl.RowHeight + 2
      End If
   Next Cl
End Sub

Hello Fluff,
I have just responded to JohnnyL's code as it does work but did not add the spacing I required. I have just tested your code and it works with the spacing I was after. So both of you have been very kind to help me out, however, trying to be fair in deciding which code provided the complete solution I think I will have to tick yours Fluff as it gave me the spacing I was after hence, a more complete solution. I hope you don't mind JohnnyL as I am grateful to you both for your assistance.
Regards
Adam

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[johnnyL]

@nitrammada Is the following basically what you are looking for? :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1												12345678901234567890 12345678901234567890 12345678901234567890 1234567890
2												123456789012345678901234567890123456789012345678901234567890
3
Sheet1

Edit: That doesn't display properly for some reason.

I am still not sure what you are looking for, but a recent post you made said that you want spaces at the end of a word wrapped cell, if I understood properly.
I have set the spaces to 10, in the above example, to easily spot the spaces at the end, as I understand that you want. But that can easily be changed. ;(

I have enclosed a pic to show what it would look like: $L1 has 70 characters, L2 has 60 characters, The column width is set to 30.

 

[Fluff]

My understanding was that if the text in a cell was longer than 65 characters then the row height should be autofit & then increased by 2

 

[johnnyL]

Hi JohnnyL, apologies for late reply, have been on holiday. Yes, your post in #7 did work thank you, but it wasn't able to add the space on after the text, that was what I was meaning by the +2. I was hoping to perhaps amalgamate yours and Fluff's code to get the desired result. Any suggestions?

I don't know @Fluff. It says after the text. Just need some clarification if the spaces are to be placed at the end of the cell or on top from the OP I guess.

 

[johnnyL]

Anywho, the following is the code I came up with to add spaces to the end of the cell:

Sub SetRowHeightOrWrapTextV3()
'
    Dim ColumnRow               As Long
    Dim LastRowInColumnL        As Long
    Dim SpacesToInsert          As Long
    Dim WidthOfColumnL          As Long
    Dim CellString              As String
'
    SpacesToInsert = 2                                                          ' <--- Set this to number of spaces desired at end of each wrapped text line
'
    LastRowInColumnL = Range("L" & Rows.Count).End(xlUp).Row                    ' Get last row in column L
    WidthOfColumnL = Columns("L").ColumnWidth                                   ' Get the width of Column
'
    For ColumnRow = 1 To LastRowInColumnL                                       ' Loop to loop through the L column
        If Len(Range("L" & ColumnRow).Value) < 65 Then                          '   If Length of cell value < 65 then ...
            Rows(ColumnRow & ":" & ColumnRow).RowHeight = 14                    '       Set the row height for that row to 14
        Else                                                                    '   Else
            For i = 1 To Len(Range("L" & ColumnRow).Value) Step WidthOfColumnL - SpacesToInsert     '       Loop to insert spaces into Temp string
                CellString = CellString & Mid(Range("L" & ColumnRow).Value, i, WidthOfColumnL - SpacesToInsert) & Space(SpacesToInsert)
            Next                                                                                    '       Loop back
'
            Range("L" & ColumnRow).Value = CellString                           '       Save Temp string with added spaces back to cell
            CellString = vbNullString                                           '       Clear the variable used for creating Temp cell string
'
            Range("L" & ColumnRow).WrapText = True                              '       Set the cell to WrapText
            Range("L" & ColumnRow).Rows.AutoFit                                 '       Resize row height to display wrapped text
        End If
    Next                                                                        ' Loop back
End Sub

 

[Fluff]

Just need some clarification if the spaces are to be placed at the end of the cell or on top

The OP does not want spaces added to the text, it's the row height that should be increased by 2 as per post#10 and the OP's response in post#12.

 

[nitrammada]

@nitrammada Is the following basically what you are looking for? :

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1												12345678901234567890 12345678901234567890 12345678901234567890 1234567890
2												123456789012345678901234567890123456789012345678901234567890
3
Sheet1

Edit: That doesn't display properly for some reason.

I am still not sure what you are looking for, but a recent post you made said that you want spaces at the end of a word wrapped cell, if I understood properly.
I have set the spaces to 10, in the above example, to easily spot the spaces at the end, as I understand that you want. But that can easily be changed. ;(

I have enclosed a pic to show what it would look like: $L1 has 70 characters, L2 has 60 characters, The column width is set to 30.

Hi JohnnyL,
Thank you for your continued assistance. It's not quite what I was after, my goal was to increase the row height so that the text would have a space of say 2 at the end of it. In old day terms we would call it a carriage return, it makes each line item easier to read, a bit like a paragraph break.
See the picture below. Row 2I, 2J & 2K have text >65 so a space after the text has been added, in other words, the row height has increased to give this space. Whereas, rows 2L, 2M, 2N & 2O have text less than 65 characters so they don't need any additional row height, they can remain as a single line. Fluff is on the right track with his understanding of what I require, and his code gave me the additional space (additional row height) I needed to make the text easier to read, not so cramped. In hindsight, I perhaps should have explained myself more clearly JohnnL, my apologies for that, thank you so much for your help, I hope I have clarified things a little.

2/I		Demolish existing 4 storey concrete frame and brick building (12 Waters Road) complete including substructure, superstructure, roof covering and plumbing, external walls and cladding, internal finishing and fittings, LFF+E, services termination and disposal of demolished material off site as required	2,879.24	m2	200	575,848
2/J		Demolish existing 3 storey concrete building (14 Waters Road) complete including substructure, superstructure, roof covering and plumbing, external walls and cladding, internal finishing and fittings, LFF+E, services termination and disposal of demolished material off site as required	2,063.61	m2	200	412,722
2/K		Remove existing metal awning complete including structural framing, roof covering, plumbing, FC soffit and disposal ofg demolished material off site as required	96.52	m2	150	14,478
2/L		Remove existing footpath, hardstanding and the like	90.08	m2	85	7,657
2/M		Remove existing trees and verge crossover	4.00	No	1,500	6,000
2/N		Remove existing external furniture as required	1.00	Item	1,000	1,000
2/O		Disposal of demolished materials off site	1.00	Item	5,000	5,000

 

[johnnyL]

Ahhhh. When you say 'end', you mean 'bottom'. Got it now. Try the following:

Sub SetRowHeightOrWrapTextV4()
'
    Dim ColumnRow           As Long
    Dim LastRowInColumnL    As Long
'
    LastRowInColumnL = Range("L" & Rows.Count).End(xlUp).Row        ' Get last row in column L

    For ColumnRow = 1 To LastRowInColumnL                           ' Loop to loop through the L column
        If Len(Range("L" & ColumnRow).Value) < 65 Then              '   If Length of cell value < 65 then ...
            Rows(ColumnRow & ":" & ColumnRow).RowHeight = 14        '       Set the row height for that row to 14
        Else                                                        '   Else
            Range("L" & ColumnRow).WrapText = True                  '       Set that cell to WrapText
            Range("L" & ColumnRow).Rows.AutoFit                     '       Resize row height to display wrapped text
            Range("L" & ColumnRow).Value = Range("L" & ColumnRow).Value & Chr(10)   ' <--- Add more '& Chr(10)' if desired
        End If
    Next                                                            ' Loop back
End Sub

That will add 'carriage return' to bottom of wrapped cell.