Export Worksheets as PDFs Ignore Blank Rows

[daveasu]

The code below will export worksheets as PDFs. However, each worksheet has formulas that result in many blank rows. These blank rows are also in the PDFs which adds several blank pages.

Is there a way to only include the rows that have data in the PDF output for each worksheet?

Also, is it possible to automatically adjust the column widths and fit them all on one page horizontally?

Sub LoopSheetsSaveAsPDF()

'Create variables
Dim ws As Worksheet

'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
For Each ws In ActiveWorkbook.Worksheets

    ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"

Next

End Sub

 

[JoeMo]

You are welcome - thanks for the reply.

As to your questions:
1. YES
2. Not sure if the syntax is right. You may need:
With ActiveSheet
.Range("A1:H1").EntireColumn.AutoFit
With .PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
End With
End With

Try it and find out.

 

[daveasu]

Joe -- I may not have those new additions to the code in the right place. It is still creating the worksheets perfectly, but the orientation is still portrait and the column width is not adjusting.

Sub LoopSheetsSaveAsPDF()
Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long, cel As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet
.Range("A1:H1").EntireColumn.AutoFit
With .PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
    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
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & Application.PathSeparator & ws.Name & ".pdf"
    ws.Rows.EntireRow.Hidden = False
    End With
End With
Next ws
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
End Sub

 

[JoeMo]

You want this to apply to ws or to the activesheet???? When you loop through worksheets using the first line below, the active sheet never changes. It remains the sheet that was active when the loop was initiated.

For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet
.Range("A1:H1").EntireColumn.AutoFit
With .PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1

 

[daveasu]

Hi Joe - thank you! I've been out in Covid quarantine.

My apologies for the confusion on ws and activesheet. I was hoping to have the landscape setting on the output PDFs?