?
Solved

VBA Macro: Set Relative Print Area

Posted on 2010-08-23
13
Medium Priority
?
2,101 Views
Last Modified: 2012-05-10
Hi,

I am trying to write a macro that will relatively set the print area to include A1:whatever column/row based on how much data is there.

How do I relatively select all the data and then set the print area?

Thanks!
0
Comment
Question by:jenpwagner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
13 Comments
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33502917
I think you can accomplish this use the macro recorder. Start recording, select A1, enable relative references, hit CTRL+Down, CTRL+Right, set the print area, turn off recorder. This assumes no skipped columns or rows...

Andrey
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33502963
Sub RelativePrintArea()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.PageSetup.PrintArea = "$A$1:" & ws.UsedRange.SpecialCells(xlCellTypeLastCell).Address
End Sub
0
 
LVL 45

Accepted Solution

by:
patrickab earned 336 total points
ID: 33502998
jenpwagner,

Try:

Sub set_pa()
    ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address
End Sub

1) Press ALT+F11
2) Find the workbook name in the VBAProject window (CTRL+R if it's not visible), right-click on the file name
3) Select Insert/Module
4) Double click on the new Module
5) Paste the code into the Module under Option Explicit
6) ALT + F11 to return to the worksheet
7) ALT + F8 to select and run the macro

Patrick
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33503116
Actually, I believe you did not set relative references on when you recorded. But I did indeed miss a step, you have to change the final result a bit. This should work:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub

If you set relative references on when you record, the only thing you have to change is Selection.Address instead of whatever the recorded gives you. Hope that works.

Andrey
0
 
LVL 6

Assisted Solution

by:andreyman3d2k
andreyman3d2k earned 332 total points
ID: 33503260
Hey, just re-read my post, and I think made it seem like you have to change something every time you run it. You don't, I was just explaining how it was created. Sorry 'bout that. This code works (same as above) for variable ranges:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address
End Sub
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33503303
Have you tried my working code? The 2nd post.
0
 
LVL 17

Assisted Solution

by:calacuccia
calacuccia earned 332 total points
ID: 33503312
Or if you want to stick with Active stuff:

Sub RelativePrintArea()
ActiveSheet.PageSetup.PrintArea = "$A$1:" & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Address
End Sub
0
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33503328
Patrick's code worked for me, I think you might have something invisible hanging out in column I. Select columns J:K, then clear all. (Edit --> Clear --> All in Excel 2003, or Home tab --> Clear --> All in Excel 2007). Then try Patrick's macro again.
0
 
LVL 6

Expert Comment

by:andreyman3d2k
ID: 33503349
Typo in my post above -- should read: might have something invisible hanging out in column K*. The instructions were correct though. The weather here is messing with my brains : )
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33503455
jenpwagner,

Thanks for the points.

BTW why are you replying as an administrator when you are the asker in this case?

Patrick
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question