VBA, PrintArea, Selection - unable to exclude cells with formulas

  • Hello all,

    I have no prior experience with Excel and I was asked for assistance by one of our Finance staff members.
    Basically, there is a workbook, which consists of multiple worksheets.
    There are several people, that have 4 worksheets assigned to them.
    The first worksheet is like a control page, where they can see their names with a button to export their worksheets to PDF.
    The columns in the worksheets are fixed A-Q, but the rows count varies.
    The original action behind the button was:

    The problem is that there are cells that have some formulas (functions?), but they appear blank, like below:

    These cells are included in the selection that is being exported and we would like avoid that, although they are not visible on the exported PDF file.
    So far, I have tried numerous ways, but every time in the Page Break view, I can see that the print area and the selection is including these cells, it works as expected on the first worksheet.
    This is what I currently have:

    And the Set_Print_Area() sub:

    I will really appreciate, if you can give me any directions or solve the problem for me :)

    Thank you in advance,

  • Re: VBA, PrintArea, Selection - unable to exclude cells with formulas

    While I was waiting for help, I helped my self :)


    Sub PDF_Kalin_Information() 
        ThisWorkbook.Sheets(Array("Open Finance - Kalin", "Completed Finance - Kalin", "Open General - Kalin", "Completed General - Kalin")).Select
        Call Set_Print_Area
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\Test\test.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True     
    End Sub

    Function Set_Print_Area()
    Dim s As Worksheet
    Dim lastCell As Long
        For Each s In ActiveWindow.SelectedSheets
             lastCell = [LOOKUP(2,1/(A1:A65536<>""),ROW(A1:A65536))]
             s.PageSetup.PrintArea = "$A$1:$Q$" & lastCell
    End Function

    Hope this will quickly help someone :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!