Format worksheet to view only the print area cells in Excel

Simple I know but have stumped everyone. How do I format a worksheet to view
only the selected print area in the working area. In other words I do not
want to see any other columns or row numbers - just a gray background which
makes the worksheet look like a clean piece of paper or form when you open it

Hide all the unused rows and or columns.
************
Anne Troy
www.OfficeArticles.com

"jshoff271" <[email protected]> wrote in message
news:[email protected]...
> Simple I know but have stumped everyone. How do I format a worksheet to
> view
> only the selected print area in the working area. In other words I do not
> want to see any other columns or row numbers - just a gray background
> which
> makes the worksheet look like a clean piece of paper or form when you open
> it

Perhaps not so simple, but relatively straightforward. You can't do it
with formatting. Either hide the unused columns/rows manually if the
user won't change the print area, or use a macro to do it:

Public Sub HideAllButPrintArea()
Dim rPrintRange As Range
Dim rFirst As Range
Dim rLast As Range

Application.ScreenUpdating = False
With ActiveSheet
.Cells.EntireColumn.Hidden = False
.Cells.EntireRow.Hidden = False
If .PageSetup.PrintArea <> "" Then
Set rPrintRange = .Range(.PageSetup.PrintArea)
Else
Set rPrintRange = .UsedRange
End If
With rPrintRange
Set rFirst = .Cells(1)
Set rLast = .Cells(.Count)
End With
If rFirst.Row > 1 Then _
.Range(.Cells(1, 1), rFirst(-0, 1)) _
.EntireRow.Hidden = True
If rFirst.Column > 1 Then _
.Range(.Cells(1, 1), rFirst(1, 0)) _
.EntireColumn.Hidden = True
If rLast.Row < .Rows.Count Then _
.Range(rLast(2, 1), .Cells(.Rows.Count, 1)) _
.EntireRow.Hidden = True
If rLast.Column < .Columns.Count Then _
.Range(rLast(1, 2), .Cells(1, .Columns.Count)) _
.EntireColumn.Hidden = True
End With
Application.ScreenUpdating = True
End Sub

This routine used the UsedRange as a proxy for PrintArea if PrintArea
has not been set. Note that UsedRange is notoriously squirrelly, and the
exact results may depend on the version of XL. In most cases, closing
then reopening the worksheet will reset the UsedRange properly.

You can have this update on opening the workbook if you also put this in
the ThisWorkbook code module (see

http://www.mcgimpsey.com/excel/modules.html

for more on where to put your code):

Private Sub Workbook_Open()
HideAllButPrintArea
End Sub

In article <[email protected]>,
jshoff271 <[email protected]> wrote:

> Simple I know but have stumped everyone. How do I format a worksheet to view
> only the selected print area in the working area. In other words I do not
> want to see any other columns or row numbers - just a gray background which
> makes the worksheet look like a clean piece of paper or form when you open it

I am not understanding this... sorry. I think I have the same question...

I want for anyone to open up the excel worksheet and see what they would if
I had pdf'd the worksheet ....

I have seen it before. Is this a template?

You can't simply hide rows and columns because the rows go on forever ...
AAAAAAAA - ZZZZZZZ to infinite it seems.

Is it ONLY possible to do this using macro's (which I have no idea how to
use)?

Thanks,
Tanya

"JE McGimpsey" wrote:

> Perhaps not so simple, but relatively straightforward. You can't do it
> with formatting. Either hide the unused columns/rows manually if the
> user won't change the print area, or use a macro to do it:
>
> Public Sub HideAllButPrintArea()
> Dim rPrintRange As Range
> Dim rFirst As Range
> Dim rLast As Range
>
> Application.ScreenUpdating = False
> With ActiveSheet
> .Cells.EntireColumn.Hidden = False
> .Cells.EntireRow.Hidden = False
> If .PageSetup.PrintArea <> "" Then
> Set rPrintRange = .Range(.PageSetup.PrintArea)
> Else
> Set rPrintRange = .UsedRange
> End If
> With rPrintRange
> Set rFirst = .Cells(1)
> Set rLast = .Cells(.Count)
> End With
> If rFirst.Row > 1 Then _
> .Range(.Cells(1, 1), rFirst(-0, 1)) _
> .EntireRow.Hidden = True
> If rFirst.Column > 1 Then _
> .Range(.Cells(1, 1), rFirst(1, 0)) _
> .EntireColumn.Hidden = True
> If rLast.Row < .Rows.Count Then _
> .Range(rLast(2, 1), .Cells(.Rows.Count, 1)) _
> .EntireRow.Hidden = True
> If rLast.Column < .Columns.Count Then _
> .Range(rLast(1, 2), .Cells(1, .Columns.Count)) _
> .EntireColumn.Hidden = True
> End With
> Application.ScreenUpdating = True
> End Sub
>
> This routine used the UsedRange as a proxy for PrintArea if PrintArea
> has not been set. Note that UsedRange is notoriously squirrelly, and the
> exact results may depend on the version of XL. In most cases, closing
> then reopening the worksheet will reset the UsedRange properly.
>
> You can have this update on opening the workbook if you also put this in
> the ThisWorkbook code module (see
>
> http://www.mcgimpsey.com/excel/modules.html
>
> for more on where to put your code):
>
> Private Sub Workbook_Open()
> HideAllButPrintArea
> End Sub
>
> In article <[email protected]>,
> jshoff271 <[email protected]> wrote:
>
> > Simple I know but have stumped everyone. How do I format a worksheet to view
> > only the selected print area in the working area. In other words I do not
> > want to see any other columns or row numbers - just a gray background which
> > makes the worksheet look like a clean piece of paper or form when you open it
>

Tanya

Info only.............

An Excel worksheet has 256 columns named A through IV

Has 65536 rows numbered 1 through 65536.

You can hide the rows and columns you don't want to see by simply selecting
them and Format>Rows>Hide and Format>Columns>Hide.

The easiest way to select unused columns and rows is to select the one
below/beside your print area then SHIFT + End + Down(or right) Arrow.


Gord Dibben Excel MVP

On Mon, 19 Dec 2005 09:57:01 -0800, "Tanya" <[email protected]>
wrote:

>I am not understanding this... sorry. I think I have the same question...
>
>I want for anyone to open up the excel worksheet and see what they would if
>I had pdf'd the worksheet ....
>
>I have seen it before. Is this a template?
>
>You can't simply hide rows and columns because the rows go on forever ...
>AAAAAAAA - ZZZZZZZ to infinite it seems.
>
>Is it ONLY possible to do this using macro's (which I have no idea how to
>use)?
>
>Thanks,
>Tanya
>
>"JE McGimpsey" wrote:
>
>> Perhaps not so simple, but relatively straightforward. You can't do it
>> with formatting. Either hide the unused columns/rows manually if the
>> user won't change the print area, or use a macro to do it:
>>
>> Public Sub HideAllButPrintArea()
>> Dim rPrintRange As Range
>> Dim rFirst As Range
>> Dim rLast As Range
>>
>> Application.ScreenUpdating = False
>> With ActiveSheet
>> .Cells.EntireColumn.Hidden = False
>> .Cells.EntireRow.Hidden = False
>> If .PageSetup.PrintArea <> "" Then
>> Set rPrintRange = .Range(.PageSetup.PrintArea)
>> Else
>> Set rPrintRange = .UsedRange
>> End If
>> With rPrintRange
>> Set rFirst = .Cells(1)
>> Set rLast = .Cells(.Count)
>> End With
>> If rFirst.Row > 1 Then _
>> .Range(.Cells(1, 1), rFirst(-0, 1)) _
>> .EntireRow.Hidden = True
>> If rFirst.Column > 1 Then _
>> .Range(.Cells(1, 1), rFirst(1, 0)) _
>> .EntireColumn.Hidden = True
>> If rLast.Row < .Rows.Count Then _
>> .Range(rLast(2, 1), .Cells(.Rows.Count, 1)) _
>> .EntireRow.Hidden = True
>> If rLast.Column < .Columns.Count Then _
>> .Range(rLast(1, 2), .Cells(1, .Columns.Count)) _
>> .EntireColumn.Hidden = True
>> End With
>> Application.ScreenUpdating = True
>> End Sub
>>
>> This routine used the UsedRange as a proxy for PrintArea if PrintArea
>> has not been set. Note that UsedRange is notoriously squirrelly, and the
>> exact results may depend on the version of XL. In most cases, closing
>> then reopening the worksheet will reset the UsedRange properly.
>>
>> You can have this update on opening the workbook if you also put this in
>> the ThisWorkbook code module (see
>>
>> http://www.mcgimpsey.com/excel/modules.html
>>
>> for more on where to put your code):
>>
>> Private Sub Workbook_Open()
>> HideAllButPrintArea
>> End Sub
>>
>> In article <[email protected]>,
>> jshoff271 <[email protected]> wrote:
>>
>> > Simple I know but have stumped everyone. How do I format a worksheet to view
>> > only the selected print area in the working area. In other words I do not
>> > want to see any other columns or row numbers - just a gray background which
>> > makes the worksheet look like a clean piece of paper or form when you open it
>>