Haven't used VBA in over a decade. I found some code for splitting a column of data into x number of rows for the indicated range. But I can't seem to find how to keep the leading zeros in the output array.

Column A contains for example:

Output comes back as:

Any help is appreciated. The inputs are all 10 characters in length, unique barcodes.

Here's the code

Sub SplitColumn()
    Dim rng As Range
    Dim InputRng As Range
    Dim OutRng As Range
    Dim xRow As Integer
    Dim xCol As Integer
    Dim xArr As Variant

    xTitleId = "Column Split"
    Set InputRng = Application.Selection
    Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
    xRow = Application.InputBox("Rows (How many rows max per column) :", xTitleId)
    Set OutRng = Range("C1")
    Set InputRng = InputRng.Columns(1)
    xCol = InputRng.Cells.Count / xRow

    ReDim xArr(1 To xRow, 1 To xCol   1)
    For i = 0 To InputRng.Cells.Count - 1
        xValue = InputRng.Cells(i   1)
        iRow = i Mod xRow
        iCol = VBA.Int(i / xRow)
        xArr(iRow   1, iCol   1) = xValue
    Next

    OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr
End Sub

CodePudding user response：

Format the output range as text:

With OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2))
    .NumberFormat = "@" 
    .Value = xArr
End With