Set print area in excel to a relative reference

Question

I want to create a loop which prints the selected range on workbook. I have a relative range selected which is stored as "Arge".

I want to set the print area as the range selected.

Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1:P37").Select
Dim Arge As Range
Set Arge = Selection             'PROBLEM
 'do loop and other code

With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = Arge            'PROBLEM with Arge

runtime error 1004: The text you entered is not a valid refrence or defined name... this error is recieved at ActiveSheet.PageSetup.PrintArea = Arge — Haider, May 27 '15 at 13:51

score 0 · Answer 1 · edited May 23 '17 at 12:14

Two things

Avoid the use of .Select. You may want to see This
the .PrintArea takes a String (Range Address) and not the Range itself. You may want to see This

Code:

Sub Sample()
    Dim ws As Worksheet
    Dim Arge As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set Arge = ws.Range("A1:P37")

    With ws.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        .PrintArea = Arge.Address
    End With
End Sub

Note:

If you really need to work with the Selection Object then ensure that it is a valid range. For example

'~~> Check if what the user selected is a valid range
If TypeName(Selection) <> "Range" Then
    MsgBox "Select a range first."
    Exit Sub
Else
    Set Arge = Selection
End If

Set print area in excel to a relative reference

1 Answers1