Macro Keeps Saving Blank Rows as Part of PDF

I have a large data set that varies each month. There's an IF formula in there that always turns up many blank cells:

Example:
=IF($B58<>"",VLOOKUP($B58,'Product Description '!$B$9:$D$40000,11,0),"")

As a result of these cells being blank, but having underlying formulas, my Excel to PDF macro keeps saving a bunch of blank pages. My goal is to have Excel convert to PDF ONLY the visible data, not the blank cells with underlying formulas.

Here is my code that results in saving my worksheet as a PDF, but giving me extra pages. What am I missing here?

Sub savepdfwithcorrectpages()

Dim ID As String

Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long, Cel As Range


For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set R = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
If Err.Number <> 0 Then
Err.Clear
GoTo Nx
End If
For Each Rw In R.Rows
For Each Cel In Rw.Cells
If Cel.Value = "" Then Ct = Ct + 1
If Ct = Rw.Cells.Count Then
Rw.EntireRow.Hidden = True
End If
Next Cel
Nx: Ct = 0

Next Rw

ID = Range("B1").Text

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Desired File\" + ID + ".pdf", _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Next ws

Application.ScreenUpdating = True

End Sub

Does this, more or less adhering to your style, work for you?
Change the references as and where required.

Please Login or Register  to view this content.