Splitting a list over 2-3 columns


Jamie B

I have a long list of names, along with other columns which I want to filter
by some of these other columns but I only need to print out the names (the
first two columns).

I do this by filtering and then selecting columns A and B as the print area.
This is fine but it doesn't look so good printing out just 2 columns per
sheet of paper - the wasted area to the right could be filled by splitting
the long list over 2 or 3 columns per sheet (hope this makes sense!)

Is this possible?



Gord Dibben

Two ways to do this.

Copy the two columns into Word and use Word's "Columns" feature.


First copy those two columns to a new worksheet then run this macro.

Makes 6 columns from the original 2

Public Sub Snake2to6()
Dim myRange As Range
Dim colsize As Long
Dim maxrow As Long
Const numgroup As Integer = 3
Const NumCols As Integer = 6
On Error GoTo fileerror
colsize = Int((ActiveSheet.UsedRange.Rows.Count + _
((NumCols - 1)) / NumCols)) / numgroup
MsgBox "Number of Rows to Move is: " & colsize
With ActiveCell.Parent.UsedRange
maxrow = .Cells(.Cells.Count).Row + 1
End With
ActiveCell.Parent.Cells(maxrow, ActiveCell.Column) _
.End(xlUp).Offset(1, 0).Select
Set myRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 2)).Address)
myRange.Cut Destination:=ActiveSheet.Range("A1") _
.Offset(0, ((NumCols) - (numgroup - 1)))
Cells.End(xlDown).Offset(1, 0).Select
Set NextRange = Range(ActiveCell.Address & ":" _
& ActiveCell.Offset(-colsize, (numgroup - 2)).Address)
NextRange.Cut Destination:=ActiveSheet.Range("A1") _
.Offset(0, (NumCols / numgroup))
Application.CutCopyMode = False
End Sub

Gord Dibben MS Excel MVP

On Mon, 9 Feb 2009 09:36:04 -0800, Jamie B <Jamie

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question