Code to set print area - why wont this work?

[ebrandt]

This code runs until this line "ActiveSheet.PageSetup.PrintArea = "A1:" & Last_Col & End_Row"
and stops. What am I doing wrong?

Sub Print_This_Analysis_Sheet()
'
' Print_Analysis_Sheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Dim Last_Col As Integer
Dim End_Row As Long

'Edit Print Area Definition to include all active bidder data cell range.
Range("C2").Select
Application.CutCopyMode = False
Cells.Find(What:="base bid plus alternates", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
End_Row = ActiveCell.Row + 4

Rows("11:11").Select
Selection.Find(What:="unresponsive", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Last_Col = ActiveCell.Column

ActiveSheet.PageSetup.PrintArea = "A1:" & Last_Col & End_Row


Range("A1").Select
Application.ActivePrinter = "\\SS-FILE3\RICOH C6501 Mail Room on Ne07:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,TRUE,,,,,,2,""\\SS-FILE3\RICOH C6501 Mail Room on Ne07:"",,TRUE,,FALSE)"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

 

[Derek Brown]

"Last_Col & End_Row" will be both numbers so you could end up with something like "A1:1015"?
Try selecting the last cell in the print area and then use "Activecell.Address" instead.

 

[ebrandt]

"Last_Col & End_Row" will be both numbers so you could end up with something like "A1:1015"?
Try selecting the last cell in the print area and then use "Activecell.Address" instead.

Thanks Derek! That was what I needed. Got it to work. Code now looks like this;

Sub Print_This_Analysis_Sheet()
'
' Print_This_Analysis_Sheet Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
Dim Last_Col As Integer
Dim End_Row As Long
Dim End_Cell As String

'Edit Print Area Definition to include all active bidder data cell range.
Range("C2").Select
Application.CutCopyMode = False
Cells.Find(What:="base bid plus alternates").Activate
End_Row = ActiveCell.Row + 4

Rows("11:11").Select
Selection.Find(What:="unresponsive", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
Last_Col = ActiveCell.Column
ActiveCell.Offset(End_Row - 11, 0).Select

End_Cell = ActiveCell.Address
ActiveSheet.PageSetup.PrintArea = "$A$1:" & End_Cell



'Print the sheet.
Range("A1").Select
Application.ActivePrinter = "\\SS-FILE3\RICOH C6501 Mail Room on Ne07:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub