Microsoft Excel - How to print spreadsheet with frozen columns

Asked By pete r. on 26-Nov-12 07:30 AM
Hello
Be glad if anyone can help on this but I have a spreadsheet for programming projects, this has the tasks on the left with the programme itself on the right - the left hand columns are frozen and the programme on the scrolls along - this means that a large number of dates can't been seen, however if I need to print the programme it prints the complete programme including the columns you can't see on the screen - I really need to print 'current view' or something but can't see anyway to do this.  I am using Excel 2007.
I have included an image of the spreadsheet so hope this is clear - be glad if anyone can help - or maybe it can't be done....
Many thanks
Peter

John D replied to pete r. on 26-Nov-12 08:17 AM
HI
Under Page Layout, you should have Print Area. You just highlight the area you want and press  Set Print Area. I'm using XL2003 so the menu is not the same but you should be able to find it
pete r. replied to John D on 26-Nov-12 09:31 AM
Thanks John D - I have tried this but it will not print on 1 page - however big the page size or small the areas selected.  Any suggestions.
Peter
John D replied to pete r. on 26-Nov-12 11:02 AM
HI
If it don't fit on one page then in the print menu reduce the 100% to something smaller. You should be able to fit it on one page. I can't see the dimension on the screenshot but if you select a large amount, the fonts will be small.
Harry Boughen replied to pete r. on 26-Nov-12 02:54 PM
Hello pete,

One option would be just to use PrintScreen or get a little open source program such as GreenShot that gives you a bit more flexibility.

Also have you tried selecting the areas as non-contiguous ranges (select the left hand part and then press CTRL and select the right hand half) and see if print will handle that. NOTE:  Just tried that it doesn't work but you could copy paste the selection to another sheet and print from there - probably should be possible to write a macro to do it automatically

Regards

Harry
Harry Boughen replied to pete r. on 26-Nov-12 06:30 PM
Hello again pete,

If you are not averse to using macros the following will do what you want.

Option Explicit

Sub test_print()
Dim rngVisible, rngToHide, rngToPrint As Range
Dim intFirstRow, intLastRow As Integer
Dim intFirstCol, intLastCol As Integer

'Determine what can be seen and get the size

Set rngVisible = ActiveWindow.VisibleRange
intFirstRow = rngVisible.Rows(1).Row
intLastRow = rngVisible.Rows(rngVisible.Rows.Count).Row
intFirstCol = rngVisible.Columns(1).Column
intLastCol = rngVisible.Columns(rngVisible.Columns.Count).Column

'Set the ranges that you need

Set rngToHide = Range(Columns(9), Columns(intFirstCol - 1))
Set rngToPrint = Range("A1").Offset(intFirstRow - 1, 0).Resize(intLastRow - intFirstRow + 1, intLastCol)
ActiveSheet.PageSetup.PrintArea = rngToPrint.Address

'Hide the unwanted columns and go to print

rngToHide.EntireColumn.Hidden = True
ActiveSheet.PrintPreview

'Restore hidden columns

rngToHide.EntireColumn.Hidden = False

End Sub

The only problem at the moment is that restoring the hidden columns loses your place in the spreadsheet but I am sure that a little more code would be possible to navigate you back to where you were.

Regards

Harry
pete r. replied to Harry Boughen on 27-Nov-12 01:22 AM
Thanks Harry that is helpful - I'll try GreenShot and see if that is a sensible solution, yes I have tried selecting the two sections of the spreadsheet as you describe and it still won't print to 1 page.
I'll try that code out though and see how that works out
Many thanks
Peter