Wrapping text within a cell in Excel using VBA

---

The most simplest way to make a cell word wrap is to right click and format the cell alignment to wrap text.  This makes the text begin on the next line within that cell when it reaches the right cell wall.  You increase the size of the cell and more text appears on the first line with less on the next.


There may, from time to time, be a case where you need to begin a new line regardless of the size of that cell.  VB needs to see a carriage return in order to accomplish this.  A carriage return is represented by Chr(10) and may be inserted into the cell directly as follows:

---

ActiveCell.FormulaR1C1 = "ABCD" & Chr(10) & "EFGH"

---

Of course, you may want to incorporate this several times based on a specific length.  Here is one way this might be accomplished:

---

Sub AddCarriageReturns()
'
' Macro created 1/13/2011 by Steve Wynne
'
Dim CarriageInsert
Dim FullString As String
Dim I As Integer
'
I = Len(ActiveCell)
Do Until I = 5
    I = I - 5
    CarriageInsert = Mid(ActiveCell, I, 5)
    FullString = CarriageInsert & Chr(10) & FullString
Loop
    ActiveCell = FullString
End Sub

---

Try to nest this within a for/next or loop statement and it can be applied to an entire range of cells!