Macro to control Print area/view

[Michael]

I have a document that is about 5,000 rows and 25 columns. In some cases I
need to print all content, and in other cases I need only to print the left
20 cloumns. I currently do this by goint into print view and moving the blue
lines into the appropriate places. I am not very familiar with Macros but
have been led to believe that this can be done using them by inserting a 2
buttons, one with a print macro for the first print view and another with the
condensed print view. I came up with an alternative solution which was to
create a seccond sheet where sheet 2 cell a1 = sheet 1 cell a1.... etc
througout the sheet area then set the print area settings accordingly, but I
am looking for a cleaner solution and some vba experience.

 

[Rick Rothstein]

These two macros (which you can put behind buttons if you want) should do
what you asked for...

Sub SetPrintArea20Columns()
Dim LastRow As Long
With ActiveSheet
.ResetAllPageBreaks
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
.PageSetup.PrintArea = "$A$1:$T$" & LastRow
End With
End Sub

Sub SetPrintArea25Columns()
Dim LastRow As Long
With ActiveSheet
.ResetAllPageBreaks
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
.PageSetup.PrintArea = "$A$1:$Y$" & LastRow
End With
End Sub

 

[Gord Dibben]

Have a look at View>Custom Views

Set up two views and switch from one to the other.

Record a macro and assign to a button.


Gord Dibben MS Excel MVP