VBA Macro: Set Relative Print Area

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!
jenpwagnerAsked:
Who is Participating?
 
patrickabConnect With a Mentor Commented:
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
 
andreyman3d2kCommented:
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
 
calacucciaCommented:
Sub RelativePrintArea()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.PageSetup.PrintArea = "$A$1:" & ws.UsedRange.SpecialCells(xlCellTypeLastCell).Address
End Sub
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
andreyman3d2kCommented:
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
 
andreyman3d2kConnect With a Mentor Commented:
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
 
calacucciaCommented:
Have you tried my working code? The 2nd post.
0
 
calacucciaConnect With a Mentor Commented:
Or if you want to stick with Active stuff:

Sub RelativePrintArea()
ActiveSheet.PageSetup.PrintArea = "$A$1:" & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Address
End Sub
0
 
andreyman3d2kCommented:
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
 
andreyman3d2kCommented:
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
 
patrickabCommented:
jenpwagner,

Thanks for the points.

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

Patrick
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.