Macro to Concatenate two columns at a time in a range

Question

I have to create a Macro which lets me Concatenate two columns at a time in a given range. For example: In range C1:Z200, I want to concatenate Column C&D, E&F, G&H and so on. How do I do it. This is my current code which only concatenate first two columns..rest remains the same.

Set Range = ActiveSheet.Range("C1:Z100")
For Each c In Range
    c.Select
    ActiveCell.FormulaR1C1 = ActiveCell & " " & ActiveCell.Offset(0, 1)
    ActiveCell.Offset(0, 1).Activate
    Selection.Clear
    ActiveCell.Offset(0, 2).Activate
Next c

Using `Range` as a variable is a bad idea since `Range` is the name of an important method and type. Also -- where are you trying to concatenate the values *to*? The first column, clearing the remaining columns? — John Coleman, Sep 13 '17 at 14:02
@JohnColeman If I am concatenating C&D then i am Concatenating to column C and then E and so on. What are the other alternatives to `.Select` — Pranjal Dixit, Sep 13 '17 at 14:09
@JohnColeman true, except using unqualified/implicit `Range` calls is an [extremely common source of errors](https://stackoverflow.com/q/8047943/1188513). At least by shadowing the global `Range` property with a local variable, OP is avoiding this common trap. OP should definitely learn to [avoid Select and Activate](https://stackoverflow.com/q/10714251/1188513) though. — Mathieu Guindon, Sep 13 '17 at 14:13
Using an array always gives better performance than looping through a range, especially if you are working with many data. If you use the method shown in my answer, you can easily write the results back. — T.M., Sep 13 '17 at 14:57

Tehscript · Answer 1 · 2017-09-13T14:35:40.693

0

Try this:

Sub Concat()
Dim i As Long, j As Long
For i = 1 To 100 'number of rows
    j = 1 'reset column to 1
    Do While j < 25 'max number of columns (until Column Y-Z)
        j = j + 2 'start from third column (Column C)
        Cells(i, j) = Cells(i, j) & " " & Cells(i, j + 1) 'concat
        Cells(i, j + 1).ClearContents 'clear
    Loop
Next i 'next row
End Sub

edited Sep 13 '17 at 14:35

answered Sep 13 '17 at 14:20

Tehscript

2,556
2
13
23

score 0 · Answer 2 · answered Sep 13 '17 at 14:39

Try this:

Sub ConcatAltCellsInAltCols()
    Dim oW As Worksheet: Set oW = ThisWorkbook.Worksheets("Sheet11")
    Dim iLC As Long: iLC = oW.Cells(1, oW.Columns.Count).End(xlToLeft).Column
    Dim iLR As Long: iLR = oW.Cells(oW.Rows.Count, 3).End(xlUp).Row
    Dim iC As Long
    Dim iR As Long

    For iR = 1 To iLR
        For iC = 3 To iLC Step 2
            oW.Cells(iR, iC).Value = oW.Cells(iR, iC) & oW.Cells(iR, iC + 1)
        Next
    Next
End Sub

T.M. · Answer 3 · 2017-09-13T15:26:15.143

0

Try this using a one based array for better Performance:

Code

Option Explicit
Sub Conc()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Concat")  ' <== change "Concat" to your sheet name to avoid subscript error
Dim v       ' variant
Dim lng As Long
Dim j   As Integer          ' corr.
' use one based array to get field data
  v = ws.Range("C1:Z100")   ' your OP range
  For lng = 1 To UBound(v)
  ' concatenate columns C&D, E&F, G&H, ...
    For j = 0 To 11
       v(lng, j * 2 + 1) = v(lng, j * 2 + 1) & v(lng, j * 2 + 2)
    Next j

  Next lng
' write array values back (overwriting D, F, H,... with the same values)
  ws.Range("C1:Z100") = v     ' your OP range
End Sub

edited Sep 13 '17 at 15:26

answered Sep 13 '17 at 14:39

T.M.

9,436
3
33
57

`v(lng, j * 2 + 1) = v(lng, j * 2 + 1) & v(lng, j * 2 + 2)` I received a runtime error "Subscript out of range" – Pranjal Dixit Sep 13 '17 at 14:50
Did you Change your sheet Name to yours? – T.M. Sep 13 '17 at 15:00
Yes I did change – Pranjal Dixit Sep 13 '17 at 15:13
@Pranjal Dixit, Did you change the proposed sheet name "Concat" to an existing work sheet you are using? Otherwise the name is missing in the worksheets collection and you receive the mentioned error. – T.M. Sep 13 '17 at 15:14
You can check the limits of your (two dimensional) array if you insert the following code line bevor the For - Next Loop: **MsgBox LBound(v, 1) & ":" & UBound(v, 1) & vbNewLine & _ LBound(v, 2) & ":" & UBound(v, 2)** --- it should be 1:100 1:24, if you are using the range C1:Z100 – T.M. Sep 13 '17 at 15:34

Macro to Concatenate two columns at a time in a range

3 Answers3