The purpose of the UDF is to receive a 1D array and split it into a 2D array. The size of the new array should be dynamic.Function SPLITARR(ByRef v() As Variant, MaxRow As Integer) As Variant  Dim ArraySize As Integer  Dim MaxCols As Integer  Dim NewArray() As Variant  Dim x As Integer, y As Integer, z As Integer  ArraySize = (UBound(v(), 1) - LBound(v(), 1)) + 1  MaxCols = ArraySize \ MaxRow  If ArraySize Mod MaxRow > 0 Then MaxCols = MaxCols + 1  ReDim NewArray(LBound(v(), 1) To MaxRow, 1 To MaxCols)  For x = LBound(v(), 1) To UBound(v(), 1)    y = x Mod MaxRow    If y = 0 Then y = MaxRow    z = x \ MaxRow    If x Mod MaxRow = 0 Then z = z - 1    NewArray(y, z + 1) = v(x, 1)  Next  SPLITARR = NewArray()End Functionand should be called like;Sub caller()Dim a() As Varianta() = Range(A4:A23)a() = SPLITARR(a(), 5)ActiveCell.Resize(UBound(a(), 1), UBound(a(), 2)).Value = a()End SubLet range A4:A23 have a value of (1,2,3,..20)and this call will return1,6,11,162,7,12,173,8,13,184,9,14,195,10,15,20
User-defined function to split a 1D array into 2D array
vba;excel
receive a 1D array and split it into a 2D array - it's not true, it receives a 2d array of which 2nd dimension is 1. It won't work receiving an 1D or transposed 2D array. (it probably serves your purpose, just be clear with the description to avoid confusion in the future.If ArraySize Mod MaxRow > 0 Then MaxCols = MaxCols + 1Although this is correct syntax it's generally a bad practice to write IF in one line.You can simplify your calculations:y = x Mod MaxRowIf y = 0 Then y = MaxRow=> y = ( (x - 1) Mod MaxRow) + 1z = x \ MaxRowIf x Mod MaxRow = 0 Then z = z - 1=> z = (x-1) \ MaxRow(These will not work correctly for 0 index, however in your case arrays copied from Range always have index starting from 1.