Concatenate Columns using VBA

[Japhet]

Hi Guys,

I need a code that will perform below requirements.

Excel VBA Concatenate Columns from A1 to AZ1 and so on.. then it will loop untill it reaches to last row e.g A10000 to B10000 and so on.. refer to below:
A1 = ABC
B1 = DEF
C1 = GHI
A2 = 123
B2 = 456
C2 = 789
result in the other sheets.
A1 = ABCDEFGHI
A2 = 123456789

Any help will be highly appreciated

Brgds,
Japhet

 

[Peter_SSs]

I have two columns which I should be able to define in the function. If I enter ConcatIf(A1:A2,B1:B2) in a single cell, the output should be

ABC - DEF
123 - 456

Please help

How do we avoid records in which either member of the pair is blank

In case it is of any use to you and you are subscribed to Office 365, then this can be done without vba using the worksheet function shown below. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. You still need to format with Wrap Text.

Excel 2016 (Windows) 32 bit
	B	C	D	E
7	ABC	14
8	DEF	20
9		26
10	GHI	34
11	JKL
12
13	MNO	26
14	PQR	34
15
16				ABC - 14 DEF - 20 GHI - 34 MNO - 26 PQR - 34
Concat

Cell Formulas
Range	Formula
E16	{=TEXTJOIN(CHAR(10),TRUE,IF((B7:B14<>"")*(C7:C14<>""),B7:B14&" - "&C7:C14,""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

The code works perfectly for me. But there is a vertical space which is seen above the output cell. I have set the vertical alignment to “top” but still the space is seen.

https://i.imgur.com/zLcpytl.jpg

Can the cell height and alignment be automatically adjusted where the =ConcatAcross function is entered

Here is my code modified to remove the Line Feed character at the top (note the only change I made is to add the code line I show in red)...

[table="width: 500"]
[tr]
	[td]Function ConcatAcross(Rng1 As Range, Rng2 As Range) As String
  Dim R As Long
  If Rng1.Rows.Count <> Rng2.Rows.Count Or Rng1.Columns.Count + Rng2.Columns.Count > 2 Then
    ConcatAcross = CVErr(xlErrRef)
  Else
    For R = 1 To Rng1.Rows.Count
      If Len(Rng1(R).Value) > 0 And Len(Rng2(R).Value) > 0 Then ConcatAcross = ConcatAcross & vbLf & Rng1(R).Value & " - " & Rng2(R).Value
    Next
    [B][COLOR="#FF0000"]ConcatAcross = Mid(ConcatAcross, 2)[/COLOR][/B]
  End If
End Function[/td]
[/tr]
[/table]

 

[melvinkoshy]

Yes, this is as per what I desired.
Also, thanks for the TEXTJOIN feature in Office 365. Is MS Excel becoming a friendlier word processing software?

 

[Peter_SSs]

.. thanks for the TEXTJOIN feature in Office 365. Is MS Excel becoming a friendlier word processing software?

You are welcome.
Who knows?