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.

Alternative...............................

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
Range("A1").Select
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)))
Range("A1").Select
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
Range("A1").Select
fileerror:
End Sub


Gord Dibben MS Excel MVP


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