VBA: Separate cell text to one word per line

[DarkJester89]

Can anybody suggest a way, VBA to apply to a range of cells into one row (B2:CT2) so it's one word per line (in the same cell).

I have a code that extends the column out but it pushes all the text to one line, so it's too long.

Sub AutoFitWrappedText()
    With Range("B2:CT2").EntireColumn
    .ColumnWidth = 255 ' maximum width
    .AutoFit
    End With
End Sub

Example Cell, what it says: Dog and Cat

Example Cell, what it looks like:

Do

g

an

cat

What I'm aiming for:

Dog

and

Cat

Any help is much appreciated and thank you in advance.

 

[Fluff]

How about

Sub AutoFitWrappedText()
   With Range("B2:CT2")
      .Replace " ", vbLf, xlPart, , , , False, False
      .EntireColumn.AutoFit
   End With
End Sub

 

[DarkJester89]

I'm not sure what happened with that, but the formulas in that range did NOT like what happened, it ref # out and corrupted the formulas

 

[Fluff]

You never said anything about formulae.
What is the formula?

 

[DarkJester89]

Oh, I was trying to keep it as a VBA code because the cells already have formula, it was much appreciated though

 

[Fluff]

It may be possible, by changing the formula if that is ok?

 

[DarkJester89]

No, it's pulling data, I can't alter the formula

 

[Fluff]

Ok.

 

[Dossfm0q]

Example

=IF(1=1,"cat and dog","")

It work with Fluff Code below

Sub AutoFitWrappedText()
   With Range("B2:CT2")
      .Replace " ", """&Char(10)&""", xlPart, , , , False, False
      .WrapText = True
      .EntireColumn.AutoFit
      .EntireRow.AutoFit
      
   End With
End Sub

 

[DarkJester89]

@Dossfm0q The VBA is replaced my formulas with the code you made, in essence, this closed vba process works, but can I get it to work with a code that's already in place?

='User Dashboard'!G2

An example, and if the reference cell, G2 is empty, it would be auto-hid, which I'm not sure if it has to be converted to search for visible columns.