VBA code to save only the visible rows to a PDF after filtering the sheet

[peteharrison86]

Hello everyone

After I've filtered my spreadsheet I want to save only the visible rows of data to a PDF (only save the results of the filtering to a PDF, and exclude empty columns outside my "table of data") Ideally also bringing up the Save As dialogue so you can choose where to save the PDF.

Is anyone able to provide the VBA code to do this?

Much appreciated,

 

[John_w]

This should work if your data starts in A1 and there is at least 1 empty column separating your 'table of data' from columns you want to exclude from the PDF.

Public Sub Save_Visible_As_PDF()

    Dim PDFfilename As Variant
   
    PDFfilename = Application.GetSaveAsFilename(InitialFileName:=ThisWorkbook.Path & "\", FileFilter:="PDF, *.pdf", Title:="Save As PDF")

    If PDFfilename <> False Then
        ActiveSheet.Range("A1").CurrentRegion.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfilename, Quality:=xlQualityStandard, _
                                                       IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False    
    End If
   
End Sub

 

[peteharrison86]

Hi John

Thank you so much that actually works perfectly! I assume it must be "Current.Region" doing the magic there and only exporting the results of your filters to PDF. I wasn't aware of the Current.Region function / property.

Thank you again,
Pete.

 

[John_w]

Yes, CurrentRegion, from a base cell or range, is a range bounded by any combination of blank rows and blank columns.