CODEWITHSUNDEEP
excelvba
Div
Div

Reputation: 55

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

This is my initial excel sheet: 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. The output I wish, which is a concatenation of column A and Column B:

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: The output after the execution of the code: I tried to skip the empty rows using If Range("A" & ActiveCell.row) <> "" but it didn't work.

Upvotes: 0

Views: 63

Answers (2)

urdearboy
urdearboy

Reputation: 14580

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

Upvotes: 1

Glenn G
Glenn G

Reputation: 667

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

Upvotes: 1

Related Questions