by Sisyphus » Mon Jan 23, 2012 10:53 pm
Hi,
This code should do the job:
- Code: Select all
Option Explicit
Public Sub Combine()
Dim J As Integer
With Selection
If .Cells.Count > 1 Then
With .Cells(1)
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = FormattedValue(.Cells(1))
End With
For J = 2 To .Cells.Count
.Cells(1).Value = .Cells(1).Value & Chr(10) _
& FormattedValue(.Cells(J))
.Cells(J).Clear
Next J
End If
End With
End Sub
Private Function FormattedValue(ByRef Cell As Range) As String
Const NumFormat As String = "0.00"
FormattedValue = Format(Cell.Value, NumFormat)
End Function
You can change the Constant NumFormat to something else (like "#,##0.00" if you want a thousands separator), and you can change the HorizontalAlignment to either xlLeft or xlCenter, if you don't like the alignment I thought you wanted.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)