Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Single Column To Multiple Columns Of X Rows

1. Member
Join Date
8th December 2007
Posts
26

## 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

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Convert Single Column To Multiple Columns

VB:
```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 at 09:42.

3. Member
Join Date
8th December 2007
Posts
26

## 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

Excel Video Tutorials / Excel Dashboards Reports

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.

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

6. Member
Join Date
8th December 2007
Posts
26

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

Hi Dave,

That works great. Thank you again.

SupraTT

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno