I am populating array from a text file from some code I found online. The plan then is to extract the array to a range. The problem is it is not working correctly. The output to the range is sideways for 8 columns and 9 rows but then the rest of it is #N/A.

The text file is 8 columns (comma delimited) by about 170000 rows.

Here is what I have so far. I am fairly certain that the problem is at the bottom where the array is assigned to the range.

Sub DelimitedTextFileToArray()
'PURPOSE: Load an Array variable with data from a delimited text file
'SOURCE: www.TheSpreadsheetGuru.com

Dim Delimiter As String
Dim TextFile As Integer
Dim FilePath As Variant 'String
Dim FileContent As String
Dim LineArray() As String
Dim DataArray() As Variant
Dim TempArray() As String
Dim rw As Long, col As Long

Application.StatusBar = False


FilePath = Application.GetOpenFilename(FileFilter:="Text File (*.txt),*..txt")
If FilePath = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If


Delimiter = ","

'Open the text file in a Read State
TextFile = FreeFile
Open FilePath For Input As TextFile

'Store file content inside a variable
FileContent = Input(LOF(TextFile), TextFile)

'Close Text File
Close TextFile

'Separate Out lines of data
LineArray() = Split(FileContent, vbCrLf)

'Read Data into an Array Variable
For x = LBound(LineArray) To UBound(LineArray)
If Len(Trim(LineArray(x))) < 0 Then

'Split up line of text by delimiter
TempArray = Split(LineArray(x), Delimiter)

'Determine how many columns are needed
col = UBound(TempArray)

'Re-Adjust Array boundaries
ReDim Preserve DataArray(col, rw)

'Load line of data into Array variable
For y = LBound(TempArray) To UBound(TempArray)
DataArray(y, rw) = TempArray(y)

Next y
End If

'Next line
rw = rw + 1



Next x

Dim Destination As Range
Set Destination = Range("a1")

Destination.Resize(UBound(DataArray, 2), UBound(DataArray, 1)).Value = DataArray




End Sub