Set Print Area macro Code

Change the "E40" to "E" & Rows.Count


Regards, TMS

Ok, then maybe add .Offset(-4, 0)

Hi romandotcom,

Try this which accounts for no cell containing the text "TOTAL VALUE" as is the case with your attached workbook:

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-general/839534-set-print-area-macro-code.html

    Dim rngFoundCell As Range

    Set rngFoundCell = Cells.Find(What:="TOTAL VALUE", _
                                LookIn:=xlFormulas, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True, _
                                SearchFormat:=False)
    
    If Not rngFoundCell Is Nothing Then
        ActiveSheet.PageSetup.PrintArea = Range("A1:E" & rngFoundCell.Row)
    Else
        MsgBox "There was no cell found containing the text ""TOTAL VALUE"" to set the print area!!", vbExclamation, "Excel v" & Application.Version
    End If
    
End Sub

HTH

Robert

Hi romandotcom,

To fix my code, simply change this line of code...

ActiveSheet.PageSetup.PrintArea = Range("A1:E" & rngFoundCell.Row)

...to this:

ActiveSheet.PageSetup.PrintArea = Range("A1:E" & rngFoundCell.Row).Address

Regards,

Robert