Concatenate Two Cells Into One Cell Everytime There Are New Data Row Added

Question

I'm having trouble to create vba code to concatenate two cells into one cell. Those two cells consists of number and text.

I already create code to import data from another workbook and make vba paste the data to the next row available by using offset(1,0). But, i have no idea how to make vba concatenate each cells in column A & B into cells in column C.

I wish to concatenate it with underscore between them: Cells A_Cells B

Thank you in advance

'Import Data
    Dim FileToOpen As Variant
    Dim openbook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", Filefilter:="Excel Files (*.xlsx),*.xlsx")
    If FileToOpen <> False Then
        Set openbook = Application.Workbooks.Open(FileToOpen)
        openbook.Sheets(1).Range("A1").CurrentRegion.Copy
        ThisWorkbook.Worksheets("NO PK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        openbook.Close False
    End If
    Application.ScreenUpdating = True

Answer 1

• Assign values to cells is more efficient than Copy/PasteSpecial.

• Apply formulas to concate Col A & B, then convert formulas to values.

Microsoft documentation:

Range.FormulaR1C1 property (Excel)

Range.Resize property (Excel)

    Dim FileToOpen As Variant
    Dim openbook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", Filefilter:="Excel Files (*.xlsx),*.xlsx")
    If FileToOpen <> False Then
        Set openbook = Application.Workbooks.Open(FileToOpen)
        Dim srcRng As Range, RowCnt As Long, ColCnt As Long
        Set srcRng = openbook.Sheets(1).Range("A1").CurrentRegion
        RowCnt = srcRng.Rows.Count
        ColCnt = srcRng.Columns.Count
        With ThisWorkbook.Worksheets("NO PK").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(RowCnt, ColCnt)
            .Value = srcRng.Value
            With .Columns(3)  ' Col C
                ' Apply formula to concate Col A, Col B
                .FormulaR1C1 = "=RC[-2] & ""-"" & RC[-1]"
                ' Convert formulas to values
                .Formula = .Value
            End With
        End With
        openbook.Close False
    End If
    Application.ScreenUpdating = True