vba - Set variables equal to Cell addresses to set print area

[Kenn]

My code should first find Cell1 & store the address to a variable, then find cell2 & store the address to another variable, then set the print area to the range between these 2 variables. A little help would be much appreciated.

Sub SetPrintArea()
    Dim Cell1 As Range
    Dim Cell2 As Range
    
    ActiveSheet.PageSetup.PrintArea = ""
    
    Range("A3").Select
    Selection.End(xlToRight).Select
    Selection.End(xlUp).Select
    Set Cell1 = ActiveCell
    
    Range("A9").Select
    Selection.End(xlDown).Select
    Set Cell2 = ActiveCell
    
    ActiveSheet.PageSetup.PrintArea = Range(Cell1 & ":" & Cell2)   ' Getting error - [B]Method 'Range' of object'_Global' failed[/B]
End Sub

Thank you.
Kenn

 

[iliace]

I think you're looking for this:

ActiveSheet.PageSetup.PrintArea = Range(Cell1.Address & ":" & Cell2.Address)

' or 

ActiveSheet.PageSetup.PrintArea = Range(Cell1, Cell2)

 

[Kenn]

Thanks iliace. Your suggestions got me thinking. I've been missing the Address part all along.

This is what worked.

ActiveSheet.PageSetup.PrintArea = Cell1.Address & ":" & Cell2.Address

Thanks a lot.
Kenn.

 

[AlphaFrog]

Another method.

[color=darkblue]Sub[/color] SetPrintArea()
    
    ActiveSheet.PageSetup.PrintArea = Range(Range("A3").End(xlToRight), Range("A9").[color=darkblue]End[/color](xlDown)).Address
    
End [color=darkblue]Sub[/color]

 

[Kenn]

Thanks AlphaFrog. That's much simpler.
I wonder if could help me a little further please. My code hides empty columns. So sometimes there may be 5 columns visible & some times over 100. How would I specify to print a maximum of 30 columns per page & to print columns A & B on all pages?

Thank you
Kenn.

 

[Rick Rothstein]

Another method.

[color=darkblue]Sub[/color] SetPrintArea()
    
    ActiveSheet.PageSetup.PrintArea = Range(Range("A3").End(xlToRight), Range("A9").[color=darkblue]End[/color](xlDown)).Address
    
End [color=darkblue]Sub[/color]

If the A3 and A9 cell references are fixed (as appears to be the case from the OP's first message), we can take advantage of that fact and employ a shortcut notation in order to compact your code line...

Sub SetPrintArea()  
  ActiveSheet.PageSetup.PrintArea = Range([A3].End(xlToRight), [A9].End(xlDown)).Address
End Sub