How to output specific data and concatenate them into a new column using Excel VBA?

Question

This is my initial excel sheet:

The output I wish, which is a concatenation of column A and Column B as you can see in column F: Note: I'll add the header "My_Items" manually.

Code:

Sub Test()

Dim lngLastRow As Long

' Selecting all rows
lngLastRow = Cells(Rows.Count, "A").End(xlUp).row

'To concatenate both columns and insert the values into a new column
Range("G2").Formula = "=A2 & "": "" & B2"
Range("G2").Copy Range("G3:G" & lngLastRow)
    
End Sub

Output after executing the code: I tried to skip the empty rows using If Range("A" & ActiveCell.row) <> "" but it didn't work.

score 1 · Answer 1 · answered Feb 22 '21 at 20:45

1

This code will cycle through the cells in column A that have something in them and concatenate the value of column A and column B into Column G

Option Explicit

Sub Test()
    Dim xlCellA As Range
    Dim LstR As Long
        Set xlCellA = Range("A2")
        LstR = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
        Do Until xlCellA.Row > LstR
            If xlCellA.Value = "" Then
                xlCellA.Offset(0, 6).Value = xlCellA.Value & ": " & xlCellA.Offset(0, 1).Value
            End If
            Set xlCellA = xlCellA.Offset(1, 0)
        Loop
        Set xlCellA = Nothing
End Sub

answered Feb 22 '21 at 20:45

Glenn G

667
10
24

No need to `Set xlCellA = Nothing` at end. The objects references will die with the macro – urdearboy Feb 22 '21 at 20:49
Unfortunately, this method didn't produce my desired output. But I can see that this method would prove to be useful to me soon. Thank you for your time! – Div Feb 22 '21 at 21:10
@urdearboy true, however it is good coding practice to make sure that your references are killed when no longer needed – Glenn G Feb 23 '21 at 15:35
1

@GlennG - I hear you but I believe this is where VBA deviates from others. Not all best practices apply everywhere. The solution in [my past question](https://stackoverflow.com/a/51066727/6706419) is worth a read :) – urdearboy Feb 23 '21 at 15:40

score 1 · Accepted Answer · answered Feb 22 '21 at 20:46

I personally think it would be better to output the result as string rather formula. Doing this in a loop (and adding your criteria of Column A not being blank) results in a macro something lke:

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update
Dim lr As Long, i As Long

lr = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For i = 2 To lr
    If ws.Range("A" & i) <> "" Then
        ws.Range("F" & i) = ws.Range("A" & i) & ": " & ws.Range("B" & i)
    End If
Next i
Application.ScreenUpdating = True

End Sub

If you do want to stick with the formula output you need to update the formula to consider the blank cells. Consider how you would do this in the excel sheet (=IF(A1<>"",A1&": "&B1,""). I.E. you formula is missing the conditional IF

How to output specific data and concatenate them into a new column using Excel VBA?

2 Answers2