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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested. If you have many formulaic, "blank rows", this could be slow. Try it on a copy of your workbook.
VBA Code:
Sub LoopSheetsSaveAsPDF()
Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    Set R = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
    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
                Ct = 0
            End If
        Next cel
    Next Rw
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & Application.PathSeparator & ws.Name & ".pdf"
    ws.Rows.EntireRow.Hidden = False
Next ws
Application.ScreenUpdating = True
End Sub
 

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
Thank you Joe! I'm getting a "Variable not defined" error for Cel?
 

keithmct

Board Regular
Joined
Mar 9, 2007
Messages
205
Office Version
  1. 2019
Platform
  1. Windows
"Cel" is "Cell" in some other language, so edit code to read cell
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just the bit in red
Rich (BB code):
Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long, Cel as Range
 

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
Fluff & JoMo - thank you! It's giving an error:
Run-time error '1004':
No cells were found.

Line:
Set R = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Fluff & JoMo - thank you! It's giving an error:
Run-time error '1004':
No cells were found.

Line:
Set R = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
That's because there are no cells containing formulas on the worksheet. Do any of your worksheets not contain formulas?

To fix the problem, replace the code with this:
VBA Code:
Sub LoopSheetsSaveAsPDF()
Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long, cel As Range
Application.ScreenUpdating = False
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
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=ThisWorkbook.Path & Application.PathSeparator & ws.Name & ".pdf"
    ws.Rows.EntireRow.Hidden = False
Next ws
Application.ScreenUpdating = True
End Sub
 

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
JoeMo thank you for the update! This did create PDFs from the first 2 worksheets, and then appeared to lock up. The first 2 worksheets contain the raw data used in the calculations. The 3rd worksheet begins the worksheets with the calculations. These worksheets have 8 columns of calculations and could contain 100 or more rows of null string data in each of the cells.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,413
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
JoeMo thank you for the update! This did create PDFs from the first 2 worksheets, and then appeared to lock up. The first 2 worksheets contain the raw data used in the calculations. The 3rd worksheet begins the worksheets with the calculations. These worksheets have 8 columns of calculations and could contain 100 or more rows of null string data in each of the cells.
As I said in an earlier post:
If you have many formulaic, "blank rows", this could be slow. Try it on a copy of your workbook.
Has the code really stopped running when it "appeared to lock up"?
 

daveasu

Board Regular
Joined
Jan 4, 2012
Messages
50
Joe, the workbook has a lot of array calculations. Any time you make any edits it goes through a recalculation. When automatic recalculation is turned off, the code worked perfectly!

Final questions:

1. Would Application.Calculation = xlManual and Application.Calculation = xlAutomatic work in the begin/end of the code?

2. Would something like this work to change the PDF output to landscape, fit to page width, and dynamically fit the column width of A:H?

With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesWide = 1
.Range("A1:H1").EntireColumn.AutoFit
End With

Thank you for your help!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,000
Members
416,004
Latest member
reitz1

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