Splitting a long list into equal columns for easier viewing

**Luke94** · 10-20-2012, 08:00 AM

I have a long list of fabrics for a price book but would like to split these into equal columns and still in alphabetical order that will fit on to an a4 page. I usually do this by playing about with it for ages to make them fit and make them all the same but I am always adding in extra fabrics or taking them out so could do with this process automating.

Would really appreciate any help

Thanks

**Richard Buttrey** · 10-20-2012, 08:39 AM

Hi,

Fitting to an A4 page can be set up in the Page Set Up area of the Ribbon. We don't know the text width of your fabrics so can't comment in how many columns across the page will look reasonable. You'll need to decide and use the Fit To 1 Page wide with the Fit to Page Tall blank, and change the number of columns in the macro below (currently set to 5). It assumes your list is in column A starting at A1.

Sub SplitIntoColumns()
    Dim lCols As Long, lListCount As Long, x As Long
    Range("C3").CurrentRegion.Clear
    
    lCols = 5
    lListCount = Range("A:A").CurrentRegion.Rows.Count / lCols

    For x = 1 To lCols
        Range("A" & x * lListCount - lListCount + 1 & ":A" & lListCount * x).Copy
        Range("A1").Cells(1, x + 2).PasteSpecial (xlPasteValues)

    Next x

End Sub

**Jakobshavn** · 10-20-2012, 09:22 AM

Say we have a long list in column A and we want to display the same items in the same order in columns B & C. In B1 enter:

=IF(ROUND(COUNTA(A:A)/2,0)>=ROW($A1),INDEX(A:A,ROW($A1),),"") and copy down

In C1 enter:

=IF(ROW($A1)+ROUND(COUNTA(A:A)/2,0)>COUNTA(A:A),"",INDEX(A:A,ROUND(COUNTA(A:A)/2,0)+ROW($A1),)) and copy down

This approach could be extended to multiple columns if desired.