Setup print area using vba

[vba317]

I am trying to setup a printarea using vba with no luck. I would appreciate so help if possible. I have tried a couple of different combinations with no success. I keep on getting a compile error on the red line.

Sub PrintAreaSetup()

Dim PR1 As String
Dim PR2 As String
Dim LR As String

LR = ActiveSheet.UsedRange.Rows.Count

PR1 = "$A$1"
PR2 = "$F" & "$" & LR
                                    
Range(PR1, PR2).Select

[red] ActiveSheet.PageSetup.PrintArea = ("PR1:PR2")[/red]

With ActiveSheet.PageSetup
.LeftFooter = "&F"
.CenterFooter = "&A"
.RightFooter = "&"""

End With
'
End Sub

 

[SkipVought]

Hi,

your code will produce unexpected results IF the UsedRange does not include the first row...

with ActiveSheet.UsedRange
  LR = .Rows.Count + .Row - 1
End with

PR1 = "$A$1"
PR2 = "$F" & "$" & LR
                                    
ActiveSheet.PageSetup.PrintArea = (PR1 & ":" & PR2)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

what about this ?

Sub PrintAreaSetup()
With ActiveSheet.PageSetup
  .PrintArea = Intersect(Range("$A:$F"),ActiveSheet.UsedRange).Address
  .LeftFooter = "&F"
  .CenterFooter = "&A"
  .RightFooter = "&"""
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886