User-defined function to split a 1D array into 2D array 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 &gt; 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 Function


and should be called like;

Sub caller()
Dim a() As Variant
a() = Range("A4:A23")
a() = SPLITARR(a(), 5)
ActiveCell.Resize(UBound(a(), 1), UBound(a(), 2)).Value = a()
End Sub


Let range "A4:A23" have a value of (1,2,3,..20)
and this call will return

1,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,20
"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 &gt; 0 Then MaxCols = MaxCols + 1


Although this is correct syntax it's generally a bad practice to write IF in one line.



You can simplify your calculations:

y = x Mod MaxRow
If y = 0 Then y = MaxRow


=> y = ( (x - 1) Mod MaxRow) + 1

z = x \ MaxRow
If 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.