CODEWITHSUNDEEP
vbaexcel
Manish
Manish

Reputation: 213

excel vba char(10) with special string value

I have a range with data and I am concatenating with line function and I want the output as follow

Range("A1:A3") has A,B,C and I want the output in single cell as

-A
-B
-C

I am using following code; somehow it is not giving me desired output. Can anyone look into this and assist?

Dim X As String,cell as Range
For Each Cell in Range("A1:A3")
  If(Len(cell.value)>0) Then
  X="-" & X & CHAR(10) & Cell.Value
End If
Next
Range("B1").Value= X

Range("B1").WrapText = True
Range("B1").Columns.AutoFit

Upvotes: 3

Views: 4616

Answers (1)

brettdj
brettdj

Reputation: 55692

Try this

X = X & "-" & cell.Value & Chr(10)

in full:

Sub ab_notsonull()
Dim X As String, cell As Range
For Each cell In Range("A1:A3")
  If (Len(cell.Value) > 0) Then
  X = X & ("-" & cell.Value & Chr(10))
End If
Next
Range("B2").Value = Left$(X, Len(X) - 1)
Range("B2").WrapText = True
Range("B2").Columns.AutoFit
End Sub

Upvotes: 5

Related Questions