Printing Columns and Rows

Thanks Anne- I didn't know the linked picture option existed!

If you want to print the "crossed" data as it appears on the worksheet, how about a couple of simple macros:

Sub MyHighlight()

Cells.Font.Color = vbWhite
Selection.EntireRow.Font.Color = vbBlack
Selection.EntireColumn.Font.Color = vbBlack

End Sub
--------------------------------------------------------
Sub HighlightOff()

Cells.Font.Color = vbBlack
End Sub
---------------------------------------------------------

Select the cell of interest, run the first macro before printing and the second after.

You could achieve a nice "grayed out" effect by changing the first line of the first macro to Cells.Font.Color = RGB(200,200,200)

These macros assume you have a black and white worksheet. You could develop a more complex macro that copied the sheet, changed the formatting on the copy (maybe making each cell's font colour the same as its fill colour), print and then delete the copied sheet, all in a "Print Selection" macro

Addendum to my comment.

For Option 2, it's not really paste/special. The feature is not listed there, but it is in the drop down menu of the paste icon as I've described.

Why not use snapshots.

I created a spreadsheet where I had done varying analysis in several tables on one sheet.

Subsequently, my boss was only interested in seeing one or two of the tables. So I snapshot the ones she was interested in and place them in another tab.

You can do this one of two ways.

You can use the "camera" feature that comes in the later versions of Excel, starting in 2010. (I'm not sure about 2007) The camera can be added to the tab by selecting Options, All Commands, and selecting 'camera', and adding it to your ribbon. ((http://www.addictivetips.com/microsoft-office/camera-tool-function-in-excel-2010/))

Selecting the rows/columns to display:
Highlight the data you want to display and click the 'camera'. Then go to the tab or area you want to place the snapshot and click there. Your data will then display like a picture, with one difference. Every time you update your data, the snapshot will update.

The 2nd option is to use the copy/paste special.

Copy the selection of data you want to isolate, and using the drop down menu from the paste Icon on the ribbon, under "other paste options" on the bottom, select linked picture (the last option on the right, the one with the link symbol. Using this option will mean that your the data in the pasted picture link will also update each time you update your data.

David, you are asking a question already dealt with in the related topic "Printing a Single Column in Multiple Columns".

Copying the data in to Word has some plus points:

Firstly, the columns will automatically 'snake' if you decide to reduce the font size (and/or increase the number of columns) so that you can get more information on one page.

Word will also cope well if you have some cells which take up more than one line of text (cell wrapping).

Word is also able to repeat header row(s) at the top of each column using the 'repeat as header row at top of each page' feature [select the row(s) Layout Tab Repeat Header Rows Button].

Also, by inserting a continuous section break at the end of the block of data, the number of rows required will be trimmed for the number of columns you have set.

For example, if you have 100 rows of data, and 40 rows will fit on a page: if you select a 3-column layout Word will display this as 40 rows in the first two columns and the remaining 20 in the third column. If you insert a continuous section break after the data [page layout Breaks Continuous] it will be displayed as two columns of 34 and a final column of 32 (which is the closest Word can get to a flush bottom as it can get).

The 'line between' option in the columnar set up is often visually attractive.

Using Excel you can use the VB script provided in the Related Topic mentioned above (for a single column of original data).

You could also try the following procedure within Excel:

Give your original data a named range -- I shall use 'Fred'.
Name a single cell 'MaxRows' and enter the number of rows you would like within each output 'column' into that cell.
Name another single cell 'Gap'. Enter the number of blank columns you would like to appear between blocks of snaked-columns in your replicated-data area.

Select the top left cell of where you would like your replicated data to appear (maybe on a new tab) and name that cell 'TopLeft'.
In that cell enter the formula:

=IF((MOD((COLUMN()-COLUMN(TopLeft)),(COLUMNS(Fred)+Gap))+1)>COLUMNS(Fred),"",OFFSET(Fred,(MaxRows*(ROUNDDOWN((COLUMN()-COLUMN(TopLeft))/(COLUMNS(Fred)+Gap),0)))+ROW()-ROW(TopLeft),(MOD((COLUMN()-COLUMN(TopLeft)),(COLUMNS(Fred)+Gap))+1)-1,1,1))

The formula in that cell can now be dragged down the requisite number of rows; and that column of formulae can then be dragged right the requisite number of columns. For example if you have a block of data which is 2 columns x 100 rows and you want it to appear as 4 blocks of 2 columns x 25 rows with one blank column gap between each block, you will drag down an extra 24 rows (25 less the cell already populated) and drag across an extra 10 columns (4 blocks of 2, ie 8, plus 3 blank columns between the 4 blocks (making 11 in total) less the column already populated with formulae).

If the last block of columns does not need to be as long as the others, just delete the surplus formulae.

To get the hang of this it is worth having the left-most column of your original data contain a serial number (1, 2, 3...). It will then be easy to see that you've copied the output formula down and across enough (and not too much).

Strictly speaking, the named range of the original data (in this case 'Fred') ONLY NEEDS TO reference the TOP-MOST ROW you would like to output, and needs to reference only the columns which you would like to output (AND NO OTHERS). The depth (number of rows) of the named range does not make any difference to the number of rows output -- that is dictated by the formulae in the cells in the output area. The named range must also be contiguous; a multi-block named range will fail.

When printing one, two or three columns in Excel, is it possible to get them to wrap on the printed page, so that 60+ rows will all automatically print on sheet of paper?
Currently I copy and paste them into MS word and use the multiple Columns feature.

Firstly, name the cell which is at the intersection of the row and the column which you want printing as "Intersection".

Then select your whole spreadsheet and create a conditional format using the following criteria:

=NOT(OR(ROW()=ROW(Intersection),COLUMN()=COLUMN(Intersection)))

using the format of white text.

If your boss wants a different row / column printing, just change the reference of the Named Range.

One of the beauties of this method is that it works for more than one row and / or column. For example, for two rows and two columns name the 2 x 2 intersection, "Intersection".

However, the rows and columns do have to be contiguous, ie the named range, Intersection, can only be one block, not multiple blocks. If you name Intersection multiple areas the conditional formatting will not operate.

To print out all rows and columns, just delete the named range.

Assume data in A5:F10 named DATA

In J5 enter formula;
=IF(OR(ROW()=$J$2+4,COLUMN()=$K$2+9),INDEX(data,ROWS($J$2:$J2),COLUMNS($J$4:J$4)),"")
and copy to J5:O10

in J2 enter the row of the data you want to print (NOT the sheet row)
in K2 enter the column of the data you want to print.

If you want to highlight the junction / intersection of the row & column you can use the following Conditional Formatting rule & set a different format.
Make this the first rule otherwise your formatting gets over written

=AND(COLUMN()=$C$2,ROW()=$B$2)

Error correction

My mistake, you need all 4 rules in place for this to work.

You can achieve this with conditional formatting.
Using 4 rules in the following order.
Use 2 cells to indicate the column & row you wish to highlight (C2 & B2 used below).
Set the formatting as you would like it to print / display. If you just want black to highlight you could just use the last 2 rules to lighten or hide the text.

=COLUMN()=$C$2
=ROW()=$B$2
set the format to a highlight text colour

=COLUMN()<>$C$4 set the text to a light grey or white - if you don't want to see it at all
=ROW()<>$B$2

If you don't understand Conditional Formatting go the "Our Products - Excel Products" link below & purchase Excel Conditional Formatting (No I don't get paid for the endorsement !!)

Hope this works for you.

Steve