Dear All,

I'm in dire need of help.

We receive Excel documents from various clients with differing layouts. Most of the files are rather messy.

What I need to do is to set the Print Area to only the cells containing actual data, set the page layout to Landscape, Page Width to 1 and print the file to PDF.

I managed to get it working for some of the files, but other files throw an error in the ActiveSheet.PageSetup.PrintArea = rMyPrintArea line of the code. I spent good six hours on it today and I'm at a dead end.

I'm attaching two files with mocked up data. "PrintAreaWorking" file is working out fine with my code, "PrintAreaProblem" file is triggering the error.

Below is the code (apologies if not elegant - I only have a very basic VBA knowledge).

I will be very grateful for some help. Thank you.

Blanka

Sub FundingReport()

Dim lRow As Long
Dim lCol As Long
Dim rMyPrintArea As Range
Dim sTPA As String

sTPA = InputBox("Enter TPA's Name")

    'Find last row
    On Error Resume Next
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    On Error GoTo 0

 
    'Find last column
    On Error Resume Next
    lCol = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column
    On Error GoTo 0
    

    Set rMyPrintArea = Range("a1:" & Cells(lRow, lCol).Address)
   

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintSheetEnd
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
        Application.PrintCommunication = True
        ActiveSheet.PageSetup.PrintArea = ""
        ActiveSheet.PageSetup.PrintArea = rMyPrintArea
        rMyPrintArea.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:="C:Funding" & sTPA & " FundingReport.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
       
  End Sub