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.
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),
underlining makes things hard to read in 16-UP at 300 dpi.
|Several topics on this page contain or refers to some VBA macros. If you need assistance to install or to use a macro please refer to my «Getting Started with Macros« or delve into it deeper on my Install page.|
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.
|The macro described here to effect snaking of columns is a VBA macro. If you need assistance to install and use a macro please refer refer to my Getting Started with Macros and User Defined Functions.|
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]
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)
for 4 lines per set start with:
for 5 lines per set start with:
for 6 lines per set start with:
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
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.
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
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)
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-3Option 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 SubThe 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.
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:
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
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.
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'more complete code at code/snakecol.txt
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.
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
- 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.
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]
'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 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 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.
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.
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.InsertRowsAndFillFormulas(Optional vRows As Long)
To unsnake Column A and B into one column (source: A1,B1, A2,B2, A3,B3, etc.
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
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
[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