CODEWITHSUNDEEP
excelvba
YasserKhalil
YasserKhalil

Reputation: 9568

Convert one column to multiple rows

I am trying to convert data in one column (say column A) to multiple rows and the results would be started in D1. This is the code till now

Sub Test()
    Dim a, e, ws As Worksheet, x As Long, i As Long
    Set ws = Sheets("Sheet1")
    a = ws.Range("A1", ws.Range("A" & Rows.Count).End(xlUp)).Value
    ReDim b(1 To UBound(a) / 5, 1 To 5)
    For Each e In a
        If x Mod 5 = 0 Then i = i + 1: x = 0
        x = x + 1
        b(i, x) = e
    Next e
    ws.Range("D1").Resize(UBound(b), 5) = b
End Sub

When the data counts as number accepts the division by 5 the code works fine. But if the number doesn't accept the division by 5 the code throws an error. How can I overcome this point? I need the extra items to go to new row even if it is one item.

Upvotes: 1

Views: 77

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

If you want to round a number up to the nearest integer then use the Ceiling.Math worksheet function from your VBA code. You can read about it in CEILING.MATH function and WorksheetFunction.Ceiling method (Excel)

You can also use Ceiling_Precise

Try this

num = Application.WorksheetFunction.Ceiling_Math(UBound(a) / 5, 1)
ReDim b(1 To num, 1 To 5)

Upvotes: 1

Related Questions