CONCAT into one cell based on 2 values

[Rick Rothstein]

   If TypeOf ConcatCol Is Range Then
        If ConcatCol.Columns.Count > 1 And ConcatCol.Rows.Count = 1 Then
            blnTranspose = True
            ConcatCol = Application.Transpose(Application.Transpose(ConcatCol.Value2))
        ElseIf ConcatCol.Columns.Count = 1 And ConcatCol.Rows.Count > 1 Then
            ConcatCol = Application.Transpose(ConcatCol.Value2)
        End If
    End If
    
    For lngLoop = LBound(ParamA) To UBound(ParamA)
        If TypeOf ParamA(lngLoop) Is Range Then
            If blnTranspose Then
                ParamA(lngLoop) = Application.Transpose(Application.Transpose(ParamA(lngLoop).Value2))
            Else
                ParamA(lngLoop) = Application.Transpose(ParamA(lngLoop).Value2)
            End If
        End If
    Next

The following comment is for the red highlighted text, but it also applies (concept-wise) to the blue highlighted text as well. You do not need to use the double Application.Transpose to assign a single row horizontal range of cells as a one-dimensional array to a variable. Here is a more direct way...

ConcatCol = Application.Index(ConcatCol.Value2, 1, 0)

Note: The Value or Value2 property must be specified or the code won't work.

[Admin]

The following comment is for the red highlighted text, but it also applies (concept-wise) to the blue highlighted text as well. You do not need to use the double Application.Transpose to assign a single row horizontal range of cells as a one-dimensional array to a variable. Here is a more direct way...

ConcatCol = Application.Index(ConcatCol.Value2, 1, 0)

Note: The Value or Value2 property must be specified or the code won't work.

Good point!