CONCATENATE with CHAR(10) IF NOT ISBLANK
 

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sincerely

Sisilla

CONCATENATE with CHAR(10) IF NOT ISBLANK
 

This should be a bit faster:

Dim vArr As Variant
Dim i As Long
With Worksheets("Data2")
With .Cells(1, 5).Resize(.Cells(.Rows.Count, 5).End(xlUp), 9)
vArr = .Value
For i = 1 To UBound(vArr, 1)
vArr(i, 1) = vArr(i, 1) & _
IIf(IsEmpty(vArr(i, 5)), "", Chr(13) & vArr(i, 5)) & _
IIf(IsEmpty(vArr(i, 9)), "", Chr(13) & vArr(i, 9))
Next i
.Resize(, 1).Value = vArr
End With
End With


In article om,
Sisilla wrote:

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sincerely

Sisilla

CONCATENATE with CHAR(10) IF NOT ISBLANK
 

Oops - I was testing in MacXL - for WinXL, change Chr(13) to Chr(10)

In article ,
JE McGimpsey wrote:

This should be a bit faster:

Dim vArr As Variant
Dim i As Long
With Worksheets("Data2")
With .Cells(1, 5).Resize(.Cells(.Rows.Count, 5).End(xlUp), 9)
vArr = .Value
For i = 1 To UBound(vArr, 1)
vArr(i, 1) = vArr(i, 1) & _
IIf(IsEmpty(vArr(i, 5)), "", Chr(13) & vArr(i, 5)) & _
IIf(IsEmpty(vArr(i, 9)), "", Chr(13) & vArr(i, 9))
Next i
.Resize(, 1).Value = vArr
End With
End With


In article om,
Sisilla wrote:

Hello All,

I am trying to concatenate the values of three cells and set the value
of the first cell to the result. The first cell is always non-empty.
If the second cell is non-empty, I concatenate a line break and its
value with the first cell. If the third cell is non-empty, I
concatenate a line break and its value with the value of the first
cell. The following For Loop runs very slowly-:

For Counter = 1 To LastRow
Set DoneBy = Worksheets("Data2").Cells(Counter, 5)
DoneBy = Worksheets("Data2").Cells(Counter, 5).Text
If Worksheets("Data2").Cells(Counter, 9).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 9).Text
End If
If Worksheets("Data2").Cells(Counter, 13).Text < "" Then
DoneBy = DoneBy.Text & Chr(10) &
Worksheets("Data2").Cells(Counter, 13).Text
End If
Next Counter

Is there a faster way to do this? The following code does not work -:

Sheets("Data2").Columns("F:F").Insert
Sheets("Data2").Range("F1:F" & LastRow).FormulaR1C1 = _
"=CONCATENATE(RC[-1], IF(ISBLANK(RC[4], """", CHAR(10)),_
RC[4], IF(ISBLANK(RC[8], """", CHAR(10)), RC[8]) "

I appreciate any effort to help me. Thank you for your time and
consideration.

Sincerely

Sisilla