Macro to concatenate

[JANE1975]

I'm trying to run a macro to concatenate two columns J&D to column K.

Ive looked through past threads and got this far, but some of my cells are blank in column J&D but would still like to combine the data.

What do I need to change to make this happen I'm guessing its something to do with the writing in green but not to sure what to change it to.

Sub CombineCols()
Dim oWS As Worksheet, lLastRow As Long, r As Long
Set oWS = ActiveSheet
lLastRow = oWS.Cells.SpecialCells(xlLastCell).row
For r = 1 To lLastRow
If Len(oWS.Cells(r, 10)) > 0 And Len(oWS.Cells(r, 4)) > 0 Then
oWS.Cells(r, 11).Value = oWS.Cells(r, 4).Value & " " & oWS.Cells(r, 10).Value
End If
Next
End Sub

 

[Rick Rothstein]

Does this macro do what you want...

[table="width: 500"]
[tr]
	[td]Sub CombineColumnsJandD()
  Dim LastRow As Long
  LastRow = Application.Max(Cells(Rows.Count, "D").End(xlUp).Row, Cells(Rows.Count, "J").End(xlUp).Row)
  Range("K1:K" & LastRow) = Evaluate(Replace("IF(LEN(J1:J#)>=0,TRIM(J1:J#&"" ""&D1:D#))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[BrianJN1]

Ok? Is there any reason that you need the IF ... End IF?
The concatenation line doesn't need to know that unless of course both the D and J values are 0 or "". Um, If there is a "0" that is going to show, so is that the reason for the IF.. END IF?

 

[Rick Rothstein]

Ok? Is there any reason that you need the IF ... End IF?
The concatenation line doesn't need to know that unless of course both the D and J values are 0 or "". Um, If there is a "0" that is going to show, so is that the reason for the IF.. END IF?

Are you asking me that question? If so... I did not use and IF..End If, what I used was an Evaluate function call which processes Excel formulas... the formula is an array formula which concatenates two cell values and then applies the TRIM function to that concatenation in case one or both of the cells in the array elements being evaluated are blank (that would leave a dangling space at on one of the ends of the concatenation which the TRIM function removes). Because Excel functions are not natively array aware, I embedded them in an IF function call which is array aware and, which in turn, induces array processing into the non-array aware text function. Note that I used a test that is always TRUE, so I was able to omit an result for the FALSE condition. The Evaluate function returns an array of values which I assign to the Column K cells.

 

[JANE1975]

Yes it does Thank you! you've saved me so much time doing this. Thanks again

 

[BrianJN1]

@Rick,
No, I wasn't asking you that question, I was replying to Jane1075. I was unaware of your presence. When I posted I found that you had posted just prior to me.

But, that you have taken the time to explain I'd wondered if in fact your code did in fact consider either D or J being zero/blank or both being so. Thank you for answering my unframed question, I'm still trying to understand arrays beyond simple things like Range("A1:C4") and Pattern matching like you and Peter_SSs often use. Always nice to get some guidance even when not specifically solicited.