Export Worksheets as PDFs Ignore Blank Rows

daveasu

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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
 
Upvote 0
"Cel" is "Cell" in some other language, so edit code to read cell
 
Upvote 0
Just the bit in red
Rich (BB code):
Dim ws As Worksheet, R As Range, Rw As Range, Ct As Long, Cel as Range
 
Upvote 0
Fluff & JoMo - thank you! It's giving an error:
Run-time error '1004':
No cells were found.

Line:
Set R = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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"?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top