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

Here's one way to accomplish that effect:

1) Fill all cell outside the print area as grey.
2) Hide the row/column headers.
3) Lock the scroll area of the worksheet to just the print area.

Let me know if you need help with any of these steps.


--
Excel_Geek


------------------------------------------------------------------------
Excel_Geek's Profile: http://www.excelforum.com/member.php...o&userid=26423
View this thread: http://www.excelforum.com/showthread...hreadid=476278

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

"jshoff271" wrote in message
...
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 ,
jshoff271 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 ,
jshoff271 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 FormatRowsHide and FormatColumnsHide.

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"
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 ,
jshoff271 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