Concatenate Columns Using VBA

Question

I have this code to concatenate data from different columns. But its not giving any results, while I have no idea what's the problem.

Things I want to do are:

1. Concatenate from Column AJ to AP with delimiter, and
2. Paste the value to Column PN in each row

  Dim wb As Workbook
  Dim ws1 As Worksheet
   
    Set wb = ThisWorkbook
    Set ws1 = ThisWorkbook.Sheets("Import")
    
    Dim srcRng As Range, RowCnt As Long, ColCnt As Long
        Set srcRng = ws1.Range("A2:AO152")
        RowCnt = srcRng.Rows.Count
        ColCnt = srcRng.Columns.Count
    
    With ws1.Range("A" & Rows.Count).End(xlUp).Resize(RowCnt, ColCnt)
        .Value = srcRng.Value
        With .Columns(320)  ' Col PN
            ' Apply formula to concate Col AJ,AK,AL,AM,AN,AO,AP
            .FormulaR1C1 = "=RC[-394] & "","" & RC[-393] & RC[-392] & "","" & RC[-391] & "","" & RC[-390] & "","" & RC[-389] & ""-"" & RC[-388]"
            ' Convert formulas to values
            .Formula = .Value
        End With
    End With

Answer 1

Concat Consecutive Columns

Sub ConcatColumns()

    Const FIRST_CELL_ADDRESS As String = "A2"
    Const CONCAT_COLUMNS As String = "AJ:AP"
    Const RETURN_COLUMN As String = "PN"
    Const DELIMITER As String = ","
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws1 As Worksheet: Set ws1 = wb.Sheets("Import")
   
    Dim rg As Range, rCount As Long
    
    With ws1.Range(FIRST_CELL_ADDRESS)
        rCount = ws1.Cells(ws1.Rows.Count, .Column).End(xlUp).Row - .Row + 1
        Set rg = .Resize(rCount)
    End With
    
    Dim Data As Variant: Data = rg.EntireRow.Columns(CONCAT_COLUMNS).Value
    
    Dim r As Long, c As Long
    
    For c = 2 To UBound(Data, 2)
        For r = 1 To rCount
            Data(r, 1) = Data(r, 1) & DELIMITER & Data(r, c)
        Next r
    Next c
    
    rg.EntireRow.Columns(RETURN_COLUMN).Value = Data
            
End Sub