(Solved) – How can I use VBA to PDF only the populated print areas within a sheet?


I am very new to VBA and trying to create a macro that will export a PDF of only the print areas that have been populated with values from another sheet in the workbook.

I have a sheet with 8 A4 forms marked out with 8 separate print areas. Depending on the project we are working on, the number of forms filled and needed to be PDF’d varies. I would like a simple macro that can detect which forms are blank, and not include them in the PDF.

I was wondering if it is possible by using VBA to detect if a cell is blank or has any value and if it does contain a value, include it in the PDF? On the first form, cell R5 is an example of a cell that if it has any value, needs to be in the PDF.

Currently I’m using this code to PDF and rename. It is cobbled together from about three different tutorials and i’m sure looks very unwieldy.

Sub GenerateSDS()


Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim lOver As Long
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & ""

strName = wsA.Range("A92").Value _
          & " _ " & "SDS" & " _ " & "BLANK"

strFile = strName & ".pdf"
strPathFile = strPath & strFile

If bFileExists(strPathFile) Then
  lOver = MsgBox("Overwrite existing file?", _
    vbQuestion   vbYesNo, "File Exists")
  If lOver <> vbYes Then

    myFile = Application.GetSaveAsFilename _
      (InitialFileName:=strPathFile, _
          FileFilter:="PDF Files (*.pdf), *.pdf", _
          Title:="Select Folder and FileName to save")
    If myFile <> "False" Then
      strPathFile = myFile
      GoTo exitHandler
    End If
  End If
End If

wsA.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strPathFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _

Sheets("Enter Data Here").Select

MsgBox "PDF file has been created: " _
  & vbCrLf _
  & strPathFile

    Exit Sub
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub
Function bFileExists(rsFullPath As String) As Boolean
  bFileExists = CBool(Len(Dir$(rsFullPath)) > 0)
End Function


Leave a Reply

Your email address will not be published. Required fields are marked *