Export Worksheets as PDFs Ignore Blank Rows

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
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?

VBA Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
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.

VBA Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 4, 2012
Messages
50
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top