Use this snippet for creating dynamic arrays from rows of data in Excel.

DArrayCreator.vb

Sub DArrayCreator()
Dim wb As Workbook
Dim ws As Worksheet
Dim i, j, k As Integer

'Use this snippet for creating dynamic arrays from rows of data in Excel.
'Current snippet only allows for two columns of data.
'Enter source workbook location here
'Set wb = Workbooks.Open("C:\Users\Martin\Desktop\ArrayReplacer.xlsx")
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

'Counts number of cells in column A
k = ws.Application.CountA(Range("A:A"))

Dim ListA(), ListB() As Variant
ReDim ListA(1 To k), ListB(1 To k)

For i = 1 To k
    ListA(i) = ws.Range("A1").Offset(i)
Next i

For i = 1 To k
    ListB(i) = ws.Range("B1").Offset(i)
Next i

'Indicates what values are included in your array.

Debug.Print "ListA has " & UBound(ListA) & " values."
Debug.Print "ListB has " & UBound(ListB) & " values."

'For i = LBound(ListA) To UBound(ListA)
 '   msg = msg & ListA(i) & vbNewLine
'Next i
'Debug.Print "The values of my dynamic array (ListA) are: " & vbNewLine & msg

End Sub