I am trying to concatenate columns with the following code (I know there are many ways to do this but am trying to follow through on this idea) but it is not working, I get "Type mismatch" here:
sTemp(x) = .Transpose(.Transpose(Intersect(UNR, UNR(i, 1).EntireRow).Value2))
Thanks
Sub CCat()
Dim r1 As Range
Set r1 = Range("A1:B1, G1:G1, I1:I1")
ConcatA "Elements", r1
End Sub
Function ConcatA(shtName As String, rng As Range) As String
Dim ws as worksheet
Dim UNR As Range
Dim sTemp() As String
Dim x As Long, i As Long
Set ws = ThisWorkbook.Sheets(shtName)
If rng Is Nothing Then Exit Function
With ws
Set UNR = Intersect(.UsedRange, rng.EntireColumn)
End With
For i = 2 To UBound(UNR.Value2)
x = x + 1
ReDim Preserve sTemp(1 To x)
With Application
sTemp(x) = .Transpose(.Transpose(Intersect(UNR, UNR(i, 1).EntireRow).Value2))
End With
Next i
Concat = Join(sTemp, ";")
'Post back to last column + 1
With ws.UsedRange.Offset(, UBound(UNR.Value2) + 1).Resize(, 1)
.Value = ConcatA
End With
End Function
Bookmarks