Column division

[SGlobal]

Hello,

I need help creating macro that selects a column, counts all the cells with in that column that have a value, then divides the range in to three blocks, evenly. Once divided, the first block (essentially the top block) stays in the same column, the second block (middle block) moves to the next column, and the third block (bottom block) moves to the third column. Can this be done?

so lets say I have a column, Column A with values in 30 cells from A1:A30. The macro should start at the top of the column count each cell that has a value and movedown until it hits an empty cell. In this case A31. After that it should take that count which should be 30 for this problem and divide by 3. Then it should leave the first 10 cells in the same column, take the next 10 cells and move to the next column, and the next 10 cells to the next column. If there were 60 cells it would leave the first 20 in the same column, move the next 20 to the second col, the next 20 to the third.

The number of cells will vary time to time. It may have 35 cells or it may have 60.

If anyone can help with this, it would be really helpful! Thanks!

 

[texasalynn]

try this

Sub SplitColumn()
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
SCol = Int(LR / 3)
Range(Cells(LR, 1), Cells(LR - SCol, 1)).Cut
ActiveSheet.Paste Range("C2")
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range(Cells(LR, 1), Cells(LR - SCol, 1)).Cut
ActiveSheet.Paste Range("B2")
MsgBox "Done"
End Sub

 

[SGlobal]

Hey, thanks for the respond. It needs a little tweaking but it works. I can tune the code a bit. Thanks alot! Really appreciate it!