CONCATENATE with CHAR(10) IF NOT ISBLANK

S

Sisilla

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
 
J

JE McGimpsey

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
 
J

JE McGimpsey

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

JE McGimpsey said:
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


Sisilla said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Concatenate Formula Using Nested IF(ISBLANK) 3
CONCATENATE with CHAR(10) IF NOT ISBLANK 6
Naming non-contigent cells with vba 5
Run Time Error '13' Type mismatch 2
Merge the contents of three non-adjacent columns 2
sub for calculating distance via post code / variable range. 7
Concatenate /w Chr(10) error 2
Set ws = ActiveSheet Compile error Can't find project or Library 8

Top