VBA to insert mutiple lines in the same celll

[VbaHell]

Hello all

I hope someone can help on this please

I am looking for some VBA that will add lines into the same cell.

In column "T" I have data that is followed by "," a comma then more data followed by "," a comma

Example:

dog, cat, horse

What I need is

dog
cat
horse

but in the same cell

Hoping someone can help on this

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG26Sep30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("T1"), Range("T" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dn.Value = Replace(Dn.Value, ", ", vbCrLf)
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[VbaHell]

Perfect Mick

Thanks very much

 

[MickG]

You're welcome

 

[Rick Rothstein]

I believe this non-looping macro will also work for you...

[table="width: 500"]
[tr]
	[td]Sub CommaSpaceToLineFeed()
  Columns("T").Replace ", ", vbLf, xlPart, , , False, False
  Intersect(Columns("T"), ActiveSheet.UsedRange).Rows.AutoFit
End Sub[/td]
[/tr]
[/table]

 

[VbaHell]

Thanks Rick, not sure how this works, but I will try this out when I'm back at my desk

 

[Rick Rothstein]

Thanks Rick, not sure how this works, but I will try this out when I'm back at my desk

It is the VBA equivalent of Excel Replace dialog box (CTRL+H).