VBA Excel: Runtime error 13 when getting range content into an array - long cell content

Question

I am trying to get the content of a row into a 1 dimensional VBA array. I used the very basic way to get the content of the row, by:

1- assigning the range content to an Excel Variant

2- transposing the array twice, to get it from 2 dimensional to 1 dimensional

This works fine in almost all my lines, but I am getting a runtime error 13 'Type mismatch' when one of the cells has a content longer than 255 caracters.

Do you by any chance have any advice on how to solve this issue?

Dim rowContent As Variant 
Dim lineCount As Long
Dim curLine As Long

lineCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("A:A")) - 2
For curLine = 3 To lineCount + 2
        rowContent = Application.Transpose(Application.Transpose(ThisWorkbook.Sheets(editWSName).Range(Cells(curLine, 2), Cells(curLine, colCount))))
        ...
Next curLine

Answer 1

So I managed to get through this problem as recommended by Rory, I added a pivot array to populate my final array.

My solution is the following:

    Dim lineCount As Long
    Dim colCount As Long
    Dim tempArray As Variant ' tableau temporaire
    Dim rowContent() As String ' array contenant le contenu de la ligne courante
    Dim i As Long
    ...

    lineCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("A:A")) - 2
    colCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(editWSName).Range("2:2"))
    fieldCodes = Application.Transpose(Application.Transpose(ThisWorkbook.Sheets(editWSName).Range(Cells(2, 2), Cells(2, colCount))))
    ReDim rowContent(1 To colCount - 1)

    ...

    For curLine = 3 To lineCount + 2
        tempArray = ThisWorkbook.Sheets(editWSName).Range(Cells(curLine, 2), Cells(curLine, colCount))
        For i = 1 To UBound(tempArray, 2)
            rowContent(i) = tempArray(1, i)
        Next i
        Set controlDict = controlLine(rowContent, fieldCodes)
        ...
    Next curLine

Answer 2

Here is a very simple way to get the content of a single row into a one-dimensional array:

Sub RowToArray()
    Dim rng As Range, s As String
    Dim msg As String

    Set rng = Rows("1:1").Cells
    s = Chr(1)

    With Application.WorksheetFunction
        arr = Split(.TextJoin(s, True, rng), s)
    End With

    msg = LBound(arr) & vbCrLf & UBound(arr)
    For Each a In arr
        msg = msg & vbCrLf & a
    Next a

    MsgBox msg
End Sub

As you see, cell D1, which contains more than 300 characters, is fully captured.