Clever Geek Handbook

Excel vba char (10) with special string value

I have a range with data and I am concatenating with a line function and I want the result to be as follows

The range ("A1: A3") has A, B, C and I want the output in a separate cell to be

-A
-B
-C

I am using the following code; anyway it doesn't give me the desired result. Can anyone look into this and help?

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
+3


source to share


1 answer


try it

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
+5


source






More articles:


All Articles