jenpwagner
asked on
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!
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!
Sub RelativePrintArea()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("S heet1")
ws.PageSetup.PrintArea = "$A$1:" & ws.UsedRange.SpecialCells( xlCellType LastCell). Address
End Sub
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("S
ws.PageSetup.PrintArea = "$A$1:" & ws.UsedRange.SpecialCells(
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)).Sel ect
Range(Selection, Selection.End(xlToRight)). Select
ActiveSheet.PageSetup.Prin tArea = 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
Sub Macro1()
'
' Macro1 Macro
'
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Sel
Range(Selection, Selection.End(xlToRight)).
ActiveSheet.PageSetup.Prin
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried my working code? The 2nd post.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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 : )
jenpwagner,
Thanks for the points.
BTW why are you replying as an administrator when you are the asker in this case?
Patrick
Thanks for the points.
BTW why are you replying as an administrator when you are the asker in this case?
Patrick
Andrey