Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2223
  • Last Modified:

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!
0
jenpwagner
Asked:
jenpwagner
  • 5
  • 3
  • 2
3 Solutions
 
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
 
patrickabCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
andreyman3d2kCommented:
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
 
calacucciaCommented:
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now