Announcement

Collapse
No announcement yet.

Convert Single Column To Multiple Columns Of X Rows

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Convert Single Column To Multiple Columns Of X Rows

    Hi All,

    I have an excel sheet with one column consisting of X rows. I want to convert this one column to N=X/25 columns.

    Thank you,

    SupraTT

  • #2
    Re: Convert Single Column To Multiple Columns

    Code:
    Sub MoveOneToMany()
        Dim lRow As Long
        Dim lColCount As Long
        Dim lRows As Long
        
        lColCount = Columns.Count
        lRow = Cells(Rows.Count, "A").End(xlUp).Row
        For lRow = 25 To lRow Step 25
            Cells(lRow, "A").Range("A1:A25").Copy _
            Cells(1, lColCount).End(xlToLeft).Cells(1, 2)
        Next lRow
        
        Range("A26:A" & lRow).Clear
    End Sub
    Last edited by Dave Hawley; February 24th, 2008, 09:42.

    Comment


    • #3
      Re: Convert Single Column To Multiple Columns Of X Rows

      Hi Dave,

      Thank you for the code. It works, but if X is not divisible by 25, then the first column has more than 25 rows. Here is what I am trying to get;

      For example, if columna A has rows 1-35 filled with numbers 1 to 35, then after running the VBA code, column A will contain numbers 1-25, and column B will contain numbers 26-35.

      Thank you,

      SupraTT

      Comment


      • #4
        Re: Convert Single Column To Multiple Columns Of X Rows

        Maybe ... in B1 and copy across and down,

        =IF(ROW() + 25*(COLUMN()-2) > COUNTA($A:$A), "", INDEX($A:$A, ROW() + 25*(COLUMN() - 2) ) )

        Then copy B1:X25 (or wherever the data ends), paste values, and delete column A.
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Convert Single Column To Multiple Columns Of X Rows

          Try it now

          Comment


          • #6
            Re: Convert Single Column To Multiple Columns Of X Rows

            Hi Dave,

            That works great. Thank you again.

            SupraTT

            Comment

            Working...
            X