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:

Sub PDF_Kalin_Information()




ThisWorkbook.Sheets(Array("Open Finance - Kalin", "Completed Finance - Kalin", "Open General - Kalin", "Completed General - Kalin")).Select




ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="W:\Test\test.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True




End Sub

The problem is that there are cells that have some formulas (functions?), but they appear blank, like below:
[ATTACH=CONFIG]72823[/ATTACH]

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:

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

And the Set_Print_Area() sub:

Sub Set_Print_Area()
    For Each Item In ActiveWindow.SelectedSheets
        Dim x As Long, lastCell As Range, LR As Long
        x = ActiveSheet.UsedRange.Columns.Count
        Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
        LR = lastCell.Row
        Do Until Application.Count(Range(Cells(LR, 2), Cells(LR, 256))) <> 0
        Set lastCell = lastCell.Offset(-1, 0)
        LR = lastCell.Row
        Loop
        ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
    Next
End Sub

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

Thank you in advance,
Kalin

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

While I was waiting for help, I helped my self

Solution:

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
    Next
End Function

Hope this will quickly help someone