Hard arrays in data | Text to Columns

Question

What i have

Let's look at an example of this code.

Sub Sample() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ws.Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlFixedWidth, _ FieldInfo:=Array( _ Array(0, 1), Array(60, 1), Array(120, 1), Array(180, 1), _ Array(240, 1), Array(300, 1), Array(360, 1), Array(420, 1) _ ), _ TrailingMinusNumbers:=True End Sub 

What I want

In a small dataset, the above code works. But what if I want to go to say Array(2700,1) ? This means that I will have to write it 46 times Array(0, 1), Array(60, 1)...Array(1080, 1)....Array(2700, 1)

What i tried

I tried to use this approach

 Sub Sample() Dim ws As Worksheet Dim MyArray Dim MyStr As String Dim i As Long For i = 0 To 2700 Step 60 MyStr = MyStr & "#" & "Array(" & i & ",1)" Next i MyStr = Mid(MyStr, 2) MyArray = Split(MyStr, "#") Set ws = ThisWorkbook.Sheets("Sheet1") ws.Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlFixedWidth, _ FieldInfo:=MyArray, _ TrailingMinusNumbers:=True End Sub 

Obviously this will not work, since Array(i,1) is stored as a string in MyArray .

My question

Is there any way to create such jagged arrays in a loop so that all I need to do is say FieldInfo:=MyArray

Answer 1

You can assign an array to an array element, for example:

 Dim n As Long n = 0 ReDim MyArray(2700 \ 60) For i = 0 To 2700 Step 60 MyArray(n) = Array(i, 1) n = n + 1 Next i 

Answer 2

You will do it right. Instead of adding a line to the loop, just create the actual elements of the array.

So this is what you will have:

 Sub Sample() Dim ws As Worksheet Dim MyArray(20) As Variant '<-- specify the number of items you want Dim i As Long For i = 0 To UBound(MyArray) MyArray(i) = Array(i * 60, 1) Next Set ws = ThisWorkbook.Sheets("Sheet1") ws.Columns(1).TextToColumns _ Destination:=Range("A1"), _ DataType:=xlFixedWidth, _ FieldInfo:=MyArray, _ TrailingMinusNumbers:=True End Sub