Solved

VBA Macro: Set Relative Print Area

Posted on 2010-08-23
13
1,939 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
  • 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 84 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
 
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 83 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 83 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now