Macro to divide column into multiple columns

Closed
Cosmo71 - Aug 21, 2010 at 12:27 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 24, 2010 at 08:10 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 24, 2010 at 08:10 AM
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
0