Macro to divide column into multiple columns

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have a monthly report which is run and exported to Excel. I have one column of data which may be between 5K-10K rows depending on number of transactions. I need to be able to divide the data into 5 separate columns starting left to right. For example:

Column "A" has 5003 rows of purchase order numbers. These need to be divided into 5 separate columns "B" through "F". As 5003 is not evenly divided by 5 columns, column"B", "C" and "D" would have 1001 rows of data and columns "E" and "F" each have 1000 rows of data.

Any idea on how to write a macro to do this? Any help is appreciated.


1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
1. First find out how many rows you have
2. for each column that you need find how many rows will fit

so for first column you have

myrows = worksheetfunction.roundup(50003/5, 0)
myrows would be 1001

so populate 1001 rows there

then for next column you have now
myrows = worksheetfunction.roundup(40002/4, 0)

and so on

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!