Printing "Snake" Columns in Excel 4

[jdegeorge]

Hi

I have an Excel file with only 2 columns. It's a 30-page document with a lot of leftover space. I'd like to see 120 rows print in column format on a page rather than just 40 rows. Is there any way to make Excel "snake" the columns before printing?

Thanks!

Jim DeGeorge

 

[KenWright]

SNAKECOLS, How to snake columns to use fewer pages

http://www.mvps.org/dmcritchie/excel/snakecol.htm

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jdegeorge]

Ken

So I have to snake the columns IN Excel before printing? I thought that it could be done only when printing. I really don't want to have to change the look of the file.


Jim DeGeorge

 

[Zathras]

Here is a quick and dirty macro to get you started:

Sub MakeThreeColumns()
Dim r As Range
Dim nRows As Long
[COLOR=green]
' Copy column headings[/color]

    Range("A1:B1").Copy
    Range("C1").PasteSpecial
    Range("E1").PasteSpecial
[COLOR=green]    
 ' Set number of rows per page[/color]
 
    nRows = 40
[COLOR=green]    
 ' Slice and dice until out of data[/color]
 
    Set r = Range(Cells(2, 1), Cells(nRows + 1, 2))
    Application.ScreenUpdating = False
    While r.Cells(1, 1) <> ""
      r.Offset(nRows, 0).Copy r.Cells(1, 3)
      r.Offset(nRows * 2, 0).Copy r.Cells(1, 5)
      r.Offset(nRows * 2, 0).Delete Shift:=xlUp
      r.Offset(nRows, 0).Delete Shift:=xlUp
      Set r = r.Offset(nRows, 0)
    Wend
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[jdegeorge]

Zathras

This macro makes 3 columns but the sorting is off.

There are 1053 items. When the macro runs, the first column contains items 1-40, 121-160, 241-280, etc., the second column contains items 41-80, 161-200, 281-320, etc.

I'm sure that "nRows" is the reason this is happening which makes is very obvious that I should

CHANGE THE LOOK OF THE FILE and use Word.

Thanks for the effort. This was the closest to working as the macros in the other links didn't even run. Enjoy the star!

Jim DeGeorge

 

[xlhelp]

Hi.

This involves a little bit of work, but is "virtually" painless.

Looks as if you have 1200 hundred rows.

Suppose you want to print the data in three double columns.

So, you will have 400 rows in each column.

Assume data is in A:B

If you activate Cell A401, HOLD SHIFT key, Pres END, Press DOWN ARROW, Press RIGHT ARROW, Move the higlighted cells to C and D. (left mouse click on the border of the highlight and drag)

Then Activate Cell C801 and repeat HOLD SHIFT key etc...

Press F5. Click Special. Select BLANKS. Click OK.

Go to Edit, Delete, Move Cells UP

Me transmitte sursum, Caledoni!

 

[jdegeorge]

xlhelp

It was a little manual, but it worked. I'm sure there's a way to turn this into a macro where the rows are counted and the copying, moving, deleting, etc. can be put in a macro, but that's WAY beyond my Excel skills. I'm sure that some posters here would agree with that one!

All in all, you deserve a star! Enjoy.

Jim DeGeorge

 

[Zathras]

jdegeorge: I took you at your word:

I'd like to see 120 rows print in column format on a page rather than just 40 rows

It sounds like that's exactly what it is doing:

the first column contains items 1-40, 121-160, 241-280, etc., the second column contains items 41-80...etc.

I made the number of lines a variable so you can set it to whatever you really need it to be. After that, all you have to do is set up the printing format for repeating line 1 at the top of every page and set the column widths as needed.

 

[KenWright]

Assuming you have 12 records in 2 columns, and your data is on a sheet called SrcSht, and you want it in 3 blocks of 2 columns of data, then on any other sheet, in cell A1 put the following formula

=OFFSET(SrcSht!$A$1,(ROW()-1)+FLOOR((COLUMN()-1)/2,1)*400,MOD(COLUMN()-1,2),)

Now just copy across to A1:F400

If you wanted 4 columns then just change 400 to 300 and copy across to A1:H300 and so on

If you have an uneven number of records, or it doesn't go exactly into the block you want to print, then use

=IF(OFFSET(SrcSht!$A$1,(ROW()-1)+FLOOR((COLUMN()-1)/2,1)*400,MOD(COLUMN()-1,2),)="","",OFFSET(SrcSht!$A$1,(ROW()-1)+FLOOR((COLUMN()-1)/2,1)*400,MOD(COLUMN()-1,2),))

and simply make sure that when you divide the number of records by the desired number of sets of columns, that round the number up (ie the 400, or 300 or whatever). That way you don't get any unsightly 0s at the end of your last set of data.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Meant 1200 records

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jdegeorge]

Ken

I'll give this a whirl and let you know how it works. I wonder if I can replace the "*400" part with a count of the original rows and then figure out some way to create a macro that includes some count to know how to evenly distribute to the proper # of columns based on the row count. That should be fun.

Thanks!

Jim DeGeorge

 

[xlhelp]

Ken, That's clever. Thanks.

Couldn't find a "hat's off to you" emoticon.

Me transmitte sursum, Caledoni!

 

[KenWright]

LOL - Thank You xlhelp

Jim - Funnily enough I went there as well, and put in a CEILING(COUNTA(SrcSht!$A:$A),10)/3 function to do exactly that, with the ceiling bit ensuring it rounded upwards so the report looked neat, but at the end of the day it started getting a bit unwieldy as you still had to decide how many columns etc, and I didn't want the formula to freak you out if it started to look like Frankenstein.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jdegeorge]

Ken...A formula that looks like Frakenstein would be welcome at this time of year!

Jim DeGeorge

 

[jdegeorge]

Ken

I tried it by inserting the COUNTA formula in place of the row value, and as long as I copy/pasted it the correct # of rows and there was an evenly-divisible number of rows, it worked great.

I tried the 2nd formual and it didn't work.

Anywho, it was fun to try but more work than it's worth. But I did learn something about a neat and complicated formula, so enjoy the star!

Jim DeGeorge

 

[jdegeorge]

Forgot to post the formula that I was trying...

=IF(OFFSET(SrcSht!$A$1,(ROW()-1)+FLOOR((COLUMN()-1)/2,1)*CEILING(COUNTA(SrcSht!$A:$A),10)/3,MOD(COLUMN()-1,2),)="","",OFFSET(SrcSht!$A$1,(ROW()-1)+FLOOR((COLUMN()-1)/2,1)*CEILING(COUNTA(SrcSht!$A:$A),10)/3,MOD(COLUMN()-1,2),))

I inserted the ceiling code you were using but it seems to be the same for both ends of the IF statement?

Jim DeGeorge