Split values into columns with setting specific nr. of rows per column

Hi,

I got a new laptop at work and forgot to save my macros from "personal" in the old one before it was gone. I could recreate all but one that was not mine, I found it somewhere online and cannot find it again for the love of me.

Basically it was a macro to split data from one cell into multiple columns, but it had 2 popups - first asked about the starting cell for the split data to start in and second asked for number of rows per column. So you would run the macro, a popup nr. 1 would ask you to select the cell for the split data to be pasted (starting point) and then popup nr. 2 came up with a text field for you to write how many rows per column you want.

E.g. you have 100 values separated by comma in cell A1.

First popup - you select cell B1.
Second popup - you write "20"
Result - the 100 values will be split in 5 columns, 20 rows per column (meaning in range B1:F20).

If you would write "30" in the second popup, the values will be in 4 columns, 30 rows per column (4th column obviously with only 10 remaining values).

I made an example, see attached.

I tried to look for it online, because I don't remember anymore where I had it from and I seem to be unable to find it again. Can someone please help?

Thanks a lot!
Kathi

Option Explicit

Sub demo()
Dim a, t() As String
Dim tblRng As Range
Dim nRows As Long, nCols As Long, nItems As Long, i As Long, j As Long, n As Long

a = [B1]

Cells.ClearContents

Set tblRng = Application.InputBox(prompt:="Start Position", Type:=8)
nRows = Application.InputBox(prompt:="Number of rows")

t = Split(a, ",")

nItems = Int(UBound(t, 1)) + 1
nCols = nItems / nRows + 1
If nItems Mod nRows = 0 Then nCols = nCols - 1

ReDim b(1 To nRows, 1 To nCols)

For i = 1 To nCols
    For j = 1 To nRows
        b(j, i) = t(n)
        n = n + 1
        If n > UBound(t, 1) Then GoTo Finish
    Next j
Next i

Finish:

tblRng.Resize(nRows, nCols) = b
[A1] = "Dataset:": [B1] = a

End Sub

OMG Thank you! You´re the best! ❤

Glad to have helped and thank you for the rep.