Solved

VBA Macro: Set Relative Print Area

Posted on 2010-08-23
13
1,984 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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

772 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