Macro to concatenate variable amount of columns

Give this a try:

Sub Wholelottasomethin()
Dim LC As Long, LR As Long, iCell As Long
Dim rng As Range, rCell As Range
Dim oldValue As String, newValue As String

Application.ScreenUpdating = False
LR = ActiveSheet.UsedRange.Rows.Count
oldValue = ""

For iCell = 2 To LR
    LC = Cells(iCell, Columns.Count).End(xlToLeft).Column
    Set rng = Range(Cells(iCell, 1), Cells(iCell, LC))
    For Each rCell In rng
        If IsEmpty(rCell) Then
            newValue = "''"
        ElseIf IsDate(rCell) Then
            newValue = "'" & Format(rCell, "mm/dd/yyyy") & "'"
        Else
            newValue = "'" & rCell & "'"
        End If
            If oldValue = "" Then
                oldValue = newValue
            Else
                oldValue = oldValue & "," & newValue
            End If
    Next rCell
    Cells(iCell, LC + 1).Value = oldValue
    oldValue = ""
Next iCell
Application.ScreenUpdating = True

End Sub