Print ____ pages wide by ___ pages tall   (#pageswide)

File, Page Setup, Page,
  Scaling: [x] Fit to ____ pages(s) wide by ___ tall

Note those are two independent settings.  You do not have to choose both.  You can choose to print 1 page wide and print as many pages as needed.  Also note use of Portrait or Landscape on the page tab.

Another page waster is being left with a lastcell (Ctrl+End) problem.  The MakeLastCell macro can be useful in eliminating unwanted rows at bottom or columns to the right to change the lastcell.

Using MS Word to Snake Columns   (#msword)

1. Select and copy columns from Excel spreadsheet.
2. Paste into Word, this takes a few moments
3. Select the rows to be repeated at top in the table, then indicate this in Word with
     Table --> Rows to repeated
4. Select entire table using anchor, or selecting cell(s) in the table and then
     Table --> Select --> Table
5. Indicate number of rows using the columns button, or
     Format --> Columns if you want more control over placement

PostScript Printers can print print 2-UP   (#PS)

File --> Print --> [Properties] --> Document Options --> Page Layout (N-Up) Options --> 2-up

I have a choice of 1-Up, 2-UP (1x2), 4-UP (2x2), 6-UP (2x3), 9-UP(3x3), 16-Up(4x4)
underlining makes things hard to read in 16-UP at 300 dpi.

Hiding intervening rows / columns   (#hiding)

Copy areas to another spreadsheet   (#copy)

Attempting to Snake columns using Excel   (#snakecols)

Since snaking columns is so easily done with the help of MS Word and with a lot more control over printing that there is not much point to trying to do this in Excel -- at least as long as Excel itself does not support this feature.  In fact the only reason to use Excel might be to keep down the proliferation of files where Word needs a separate file for each document, and Excel has separate documents as individual sheets.

Snakecols is incomplete (coded Jan 1999).  As the macro currently stands you will have to modify the macro to specify some parameters such as number of rows on a page, how many heading lines, how many columns in the original.  Macro coding can be found in snakecol.txt and you can experiment using test data in snaketstcsv.txt test data.

Sub SnakeCols_test()
'Parameters for SnakeColsx 
'Hrows = 2     'specify number of heading rows
'Cols = 3      'specify number of cols to copy 
'setts = 4     'specify number of sets per page
'rowspp = 50   'specify number of rows per page
'ptsize = 0    'specify pointsize, must be > 6 to change
 Application.Run "personal.xls!SnakeColsx", 2, 3, 4, 50
End Sub        '[an extra column was included for spacing]

Creating a Table from something like a column of address labels   (#snkAddr)

Simple example to take a single column, such as labels with Name; Street Address; City, State, Zipcode and convert to a table.    

On a second sheet, then use fill-handle for 3 lines per set start with:  (this is the example at right) A1: =OFFSET(contacts!$A$1,ROW()*3-4+COLUMN(),0)&"" for 4 lines per set start with: A1: =OFFSET(contacts!$A$1,ROW()*4-5+COLUMN(),0)&"" for 5 lines per set start with: A1: =OFFSET(contacts!$A$1,ROW()*5-6+COLUMN(),0)&"" for 6 lines per set start with: A1: =OFFSET(contacts!$A$1,ROW()*6-7+COLUMN(),0)&"" The concatenation of a null string at the end prevents a blank cell from producing a zero. Another Solution, must be placed in cell B1 of same sheet #, then use fill-handle B1:  =INDIRECT("a"&ROW()*3-(4-COLUMN())) Use the fill handle to copy down until you run out of contacts you can find the last cell on your Contacts sheet (ctrl+End) and divide the row by 3 to see how far down you will have to copy on your second sheet. After you have completed this process your second sheet will be dependent on content and existence of your contacts sheet. To remove this dependency. Select all cells (ctrl+A) then Edit, paste special, values. You will now have constants without dependencies (no formulas). Select row 1, Insert, rows; and then provide titles for your columns.

Before   A B 1 A1   2 A2   3 A3   4 A4   5 A5   6 A6   7 A7   8 A8   9 A9   10 A10   11 A11   12 A12

After   A B C D 1 A1 A2 A3   2 A4 A5 A6   3 A7 A8 A9   4 A10 A11 A12   5 A13 A14 A15   6 A16 A17 A18   7 A19 A20 A21   8 A22 A23 A24   9 A25 A26 A27   10 A28 A29 A30   11 A31 A32 A33   12 A34 A35 A36

To create a new worksheet from name and address label format using a macro see code for naddr3ss in my code directory.  A variation that has 8 rows per set and each row has a descriptive title that would be used as the header for the column naddr8fss also seen in newsgroup as response

Something along the lines of a worksheet formula   (#WF)

This example only runs across but not down, but might give a better idea of why you would want a macro and secondly how you could do this with worksheet formulas and the fill handle.  But any insertion or deletion of row would affect these formulas so you would have to use INDIRECT and of course we cannot process for more than a single row of displayed data with these formulas.

	A	B	C	D	E	F	G	H	I	J	K
12	A12	B12	A24	B24	A36	B36	A48	B48	A60	B60	A72
13	=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
14	=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
15			=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
16			=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
17					=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
18					=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)
19							=OFFSET(sheet33!$A$12,(COLUMN()-1)/2*12,0)
20							=OFFSET(sheet33!$B$12,(COLUMN()-2)/2*12,0)

Reformatting 5 rows per set to 1 row (#reformat)

A B C  1  A1  B1-2  C1-3  2  A2  B2-2  C2-3  3  A3  B3-2  C3-3  4  A4  B4-2  C4-3  5  A5  B5-2  C5-3  6  A6  B6-2  C6-3  7  A7  B7-2  C7-3  8  A8  B8-2  C8-3  9  A9  B9-2  C9-3 10  A10  B10-2  C10-3     A B C D E F G  1  A1  A2  A3  A4  A5  B1-2  C1-3  2  A6  A7  A8  A9  A10  B6-2  C6-3  3  A11  A12  A13  A14  A15  B11-2  C11-3  4  A16  A17  A18  A19  A20  B16-2  C16-3  5  A21  A22  A23  A24  A25  B21-2  C21-3

Option Explicit
Public Sub N511_7()
'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie,  2004-11-19,  programming
' http://www.mvps.org/dmcritchie/snake.htm#reformat
'      A1, A2, A3, A4,  A5, B1, C1   to 1st row
'      A6, A7, A8, A9, A10, B6, C6  to 2nd row,   etc.
Dim cRow As Long, newRows As Long, lastrow As Long
Dim wsSource As Worksheet
lastrow = Cells.SpecialCells(xlLastCell).Row
newRows = Int((lastrow + 4) / 5)
Set wsSource = ActiveSheet
Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For cRow = 1 To newRows
    'pick up cells from Column A
    Cells(cRow, 1) = wsSource.Cells(cRow * 5 - 4, 1)
    Cells(cRow, 2) = wsSource.Cells(cRow * 5 - 3, 1)
    Cells(cRow, 3) = wsSource.Cells(cRow * 5 - 2, 1)
    Cells(cRow, 4) = wsSource.Cells(cRow * 5 - 1, 1)
    Cells(cRow, 5) = wsSource.Cells(cRow * 5 - 0, 1)
    'additional cells from column B & C of each first row 
    Cells(cRow, 6) = wsSource.Cells(cRow * 5 - 4, 2)
    Cells(cRow, 7) = wsSource.Cells(cRow * 5 - 4, 3)
Next cRow
Cells.EntireColumn.AutoFit
Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = True
End Sub

The macro is suitable for converting a 1-up name and addressed label format to a name and address per row suitable for use in Mail Merge to print address or other labels.  There are additional examples of similar things on this (snakecol) web page.

Splitting up the First 3 columns to separate rows   (#splitting)

Original Sheet   A B C D E F G H I J K 1 A1   B1   C1   D1   E1   F1   G1   H1   I1   J1   K1   2 A2   B2   C2   D2   E2   F2   G2   H2   I2   J2   K2   3 A3   B3   C3   D3   E3   F3   G3   H3   I3   J3   K3   4 A4   B4   C4   D4   E4   F4   G4   H4   I4   J4   K4   5 A5   B5   C5   D5   E5   F5   G5   H5   I5   J5   K5   6 A6   B6   C6   D6   E6   F6   G6   H6   I6   J6   K6   7 A7   B7   C7   D7   E7   F7   G7   H7   I7   J7   K7     New Sheet Formulas: A1:  =OFFSET(OrigSheet!$A$1,INT((ROW()-1)/3),MOD(ROW()-1,3))   B2:  =OFFSET(OrigSheet!D$1,INT((ROW()-1)/3),0)		New Sheet   A B C D E F G H I 1 A1  D1  E1  F1  G1  H1  I1  J1  K1  2 B1  D1  E1  F1  G1  H1  I1  J1  K1  3 C1  D1  E1  F1  G1  H1  I1  J1  K1  4 A2  D2  E2  F2  G2  H2  I2  J2  K2  5 B2  D2  E2  F2  G2  H2  I2  J2  K2  6 C2  D2  E2  F2  G2  H2  I2  J2  K2  7 A3  D3  E3  F3  G3  H3  I3  J3  K3  8 B3  D3  E3  F3  G3  H3  I3  J3  K3  9 C3  D3  E3  F3  G3  H3  I3  J3  K3  10 A4  D4  E4  F4  G4  H4  I4  J4  K4  11 B4  D4  E4  F4  G4  H4  I4  J4  K4  12 C4  D4  E4  F4  G4  H4  I4  J4  K4  13 A5  D5  E5  F5  G5  H5  I5  J5  K5  14 B5  D5  E5  F5  G5  H5  I5  J5  K5

Joining Codes in Column B to a Product in Column A   (#joining)

A B 1 111111 12 2 111111 13 3 111111 13 4 222222 11 5 333333 12 6 333333 12

A B 1 111111 12, 13, 13 2 222222 11 3 333333 12,12

JOINCODE macro creates a new sheet.   Macro joins Column B to a Product in Column A.  Coding can be found at http://www.mvps.org/dmcritchie/excel/code/joincode.txt A variation (Dave Peterson 2004-11-06) with 5 colums to check for equality then combine the unique 6th column.

Clear Duplicate A and B column cells   (#cleardupAandB)

Sub cleardupAandB()
  'D.McRitchie, 2004-08-11 snakecol.htm
  Dim lastrow As Long, i As Long
  lastrow = Cells(Cells.Rows.Count, _
     "A").End(xlUp).Row
  For i = lastrow To 2 Step -1
    If Cells(i, 1) = Cells(i - 1, 1) _
      And Cells(i, 2) = Cells(i - 1, 2) Then
        Cells(i, 1) = ""
        Cells(i, 2) = ""
    End If
  Next i
End Sub

For a more generic solution see Fill in the Empty Cells, this macro will fill in empty cells with the content of the cell above it, providing the cell above is also within the selection range.  Also a macro to clear cell if a duplicate of the cell above.

Text to Columns, Split at LF (line=feed character) (#lf)

If you used Ctrl+Alt to place like 4 lines of data into a cell and now discover you need to have them all on one row for a Mail Merge, you can use Text to Columns to spread the cell content to the columns to right.

A  1  John Doe  111 Smith Drive  Two Horses, Utah      UT 12344-6789

• Select the column
• Data, text to columns, delimited, the delimiter would be [x] Other and you would enter CHAR(10)
  by using Alt+0010 on numeric keypad
  on a laptop use Fn + Alt + 0010 on the numeric keypad (blue numbers)
• Finish, You might then want to run the TrimALL macro.

Split Cells with LF in Column A (and B) into new rows   (#split10_to_rows)

Spit cells with LF Char(10) into new rows based on content in Column A, will split B as well if there is a cell to be broken apart in Column A. [macro]

Creating a Spreadsheet from Database data   (#dbdata)

'Convert 1-Up Name and Address labels to Spread Sheet format.
'David McRitchie  http://www.mvps.org/dmcritchie/excel/code/naddrdb.txt
'   2002-05-05 NAddrDB macro work with names as arg in A and value in B
'    will accept Arguments in any order within blank row delimited ranges

A B 1 Name Rogers 2 Dob 19/06/54 3 Address 54 High St 4 Tel 218937 5 Fax 2187398 6 Email james@mail 7     8 Name Smith 9 Dob 05/02/69 10 Tel 7557254 11 Fax 454566 12 Address 21 New Road

A B C D E F 1 Name Dob Address Tel Fax Email 2 Rogers 19/06/54 54 High St 218937 2187398 james@mail 3 Smith 5/2/1969 21 New Road 7557254 454566   Obtain NADDRDB macro and install. Instructions to install a macro can be found on my getstarted.htm page.  For even more information on macro see Install Macros and User Defined Functions, which used to be part of my Dana DeLouis provides another solution in the same thread.  Timings are about the same when including turning off calculations and screen updating. A variation of this was that there were several address lines all with the same tag ("infos") and all were to be included separately.  See reply post 2003-07-22 modification based on NaddrDB.

An incomplete CSV file within Column A (#PopulateAddr3Data)

Data in Column A looks like this
FirstName, Last Name, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, Country

Should have looked like this
FirstName, Last Name,,,, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2,, City, ST Zip, Country
FirstName, Last Name, Addr1, Addr2, Addr3, City, ST Zip, Country

A macro to add the missing commas and then invoke Text to columns with a comma delimiter and not ignore duplicate delimiters can be found in my posted reply 2004-08-29, programming.

Unsnake Columns   (#unsnake)

Although this was probably covered earlier, here ae some simple examples to unsnake a worksheet from another worksheet with Worksheet Formulas.  Can also be used on same worksheet if you want to see original and results.

To unsnake Column A and B into one column (source: A1,B1, A2,B2, A3,B3, etc.
  =OFFSET(OrigSheet!$A$1,INT((ROW()-1)/2),MOD(ROW()-1,2))

Simplify data for Pivot Table Use (#simplify)

Sub Split_2_splits()
  'David McRitchie, 2006-03-28, snakecol.htm 
  Dim stub_columns As Long, arg_columns As Long
  Dim lastrow As Long
  stub_columns = 3  'columns A:C
  arg_columns = 3   'columns D for 3 columns
  Dim oldSht As Worksheet, newSht As Worksheet
  Dim r As Long, c As Long, nr As Long, ac As Long
  Dim ac_from As Long, ac_to As Long
  ac_from = stub_columns + 1
  ac_to = stub_columns + arg_columns
  lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
  nr = 0
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Set oldSht = ActiveSheet
  Application.DisplayAlerts = False
  On Error Resume Next
  Sheets("new_work").Delete
  On Error GoTo 0
  Application.DisplayAlerts = True
  ActiveWorkbook.Worksheets.Add(After:=ActiveSheet).Name _
     = "new_work"
  Set newSht = ActiveSheet
  For r = 1 To lastrow
    For ac = ac_from To ac_to
      If Trim(oldSht.Cells(r, ac)) <> "" Then
         nr = nr + 1
         For c = 1 To stub_columns
            newSht.Cells(nr, c) = oldSht.Cells(r, c)
            newSht.Cells(nr, c).Select
            oldSht.Cells(r, c).Copy
            newSht.Cells(nr, c).PasteSpecial _
              Paste:=xlPasteFormats, Operation:=xlNone, _
              SkipBlanks:=False, Transpose:=False
         Next c
         newSht.Cells(nr, ac_from) = oldSht.Cells(r, ac)
         newSht.Cells(nr, ac_from).Select
         oldSht.Cells(r, ac).Copy
         newSht.Cells(nr, ac_from).PasteSpecial Paste:=xlPasteFormats, _
            Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False        
      End If
    Next ac
  Next r
  newSht.Activate
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
End Sub

Related Information - Snakecols     (#related)

• On this page (Snakecols), portions oriented to making a readable spreadsheet, not necessarily toward saving paper.:  
    Creating a Spreadsheet from Database data (above)
    Creating a Table from something like a column of address labels (above).  . 
• Test data, snaketstcsv.txt, was created using MarkCells().  Markcells is a useful utility because it allows you to create data that helps you see original location of cells before rearranging them with a program, or a macro such as snakecols.  A technique I have used in programming and in Excel.
• John Walkenbach has a utility named 'Transform Vertical Range' in his PUP 2000 add-in on his web site.  (pay for)
• Creating a database table from a summary table, John Walkenback, Tip 68, Reverse of a Pivot Table, puts them back to detail (though summarized).
• Tom Ogilvy has posted formulas from time to time on snaking columns, this formula is an interesting variation posted 2001-06-08, that will provide the combinations used in Columns A & B (use fill-handle).
  =INDEX(A:A,INT((ROW()-1)/COUNTIF(B:B,"<>"))+1,1)&" "&OFFSET($B$1,MOD(ROW()-1,COUNTIF(B:B,"<>")),0)
• Combine rows, based on Column A posted by Patrick Molloy combines B column as comma separated values for rows that have same column A value (merging cells then deleting rows).  (See sumdata.htm for similar things.)
• naddr2ss (macro code).  Convert 1-up labels to Spreadsheet format.  The 1-Up labels must be separated by at least one blank line to start a new spreadsheet row.
• BuildData  macro,  puts column A entries in B,C,D and Column B entries in E,F,G.  Posted by Tom Ogilvy, misc, 1999-12-31.
• ASAP Utilities, add-in by Bastien Mensink, often mentioned as an alternative to snake columns macro.
• How to use macros to sort data in multiple columns as one column in Excel (KB 247311), sort a newspaper (snaking) columns of data as if one column.
• XL2HTMLa, generate separate HTML tables, one for each selection area. (look for XL2HTMLa)

It should be rather apparent that Excel does not do a good job at printing name and address labels; whereas, Microsoft Word does have adequate label printing facilities.  Microsoft expects you to use Word to print labels and to use Excel as the database.  For a comprehensive treatment of printing labels with Word on my site see Mail Merge, some notes on Mail Merge and Excel

Related Information - Rearranging Data within same row

• Reformat or Rearrange Data in Columns. (same descriptions as in excel.htm)
  Macros especially useful for reformatting name and address listings, and for creating test data.  Rearrange columns by splitting, joining, or reversing columns of data.  Join() can be used as a reversal of a correctly executed text to columns.  Lastname() can be used as a put lastname first is not already done.  FixUSzip5() corrects US 5-digit zipcodes by conversion to 5 digit numbers as text.  ReEnter() equivalent to F2 then Enter, includes some specialized versions (also see Number Conversions page).  SepTerm() can be used as a more limited version of text to columns that only separates from the first word. SepLastTerm() separates last word from beginning of text.  TrimALL() is used to TRIM leading and trailing spaces from selected range.  ReversI() is used to reverse the order of items in a row, column, or range.  RotateCW() is used to rotate a range of cells 90 degrees preserving formulas and formats.  Selection area must include cell A1.  MarkCells() is used to create test data within the invoked range.  MarkSepAreas() includes cell address and area number for creating test data across multiple ranges.  i.e. A1-1, B1-1, B2-2,C2-2,D2-2.  Additional material includes use of fill handle and creating a sheet with short cut keys for reference.  FillSequence() numbers cells 1,2,3, etc. for selected range(s).  ReproduceActive() Reproduce Formula in ActiveCell to selected ranges(s).

[my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2006,  F. David McRitchie,  All Rights Reserved