Click here to Skip to main content
15,882,721 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I created 2 sheets in Excel. Then, I used the VBA below to export the 2 sheets as a PDF
HTML
Sub Macro1()
   Sheets("Sheet_1").Activate
    ActiveSheet.UsedRange.Select
   Sheets("Sheet_2").Activate
   ActiveSheet.UsedRange.Select

   ThisWorkbook.Sheets(Array("Sheet_1", "Sheet_2")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Temp\MyPDF.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

But the exported PDF contains 4 pages, of which Pages 2 and 4 are actually empty. How can I format it as 2 pages only with the elimination of the empty pages? Thanks.

What I have tried:

Excel - Export multiple sheets as a PDF - How to eliminate the empty pages
Posted
Updated 18-Jul-16 9:03am

1 solution

Your sheets have some cells in right or bottom that display nothing but are not empty, so these cells are included in what you want to print.
Type Ctrl-End to go to last cell, you will see it is bigger than you expect.
Either you remove those cells, either you define the print zone for each sheets.
 
Share this answer
 
Comments
s yu 18-Jul-16 15:53pm    
Thanks for your response. Following yours, I clicked Ctr+End, it points to Cell (30, J). Then I deleted all after (19, H), which is my active used range, the Ctr+End still points (30 ,J). Any additional hint? Thanks.
Patrice T 18-Jul-16 15:55pm    
you need to save the file to update the end.
I recommend to suppress the unneeded rows ans columns.
s yu 19-Jul-16 6:47am    
Prob. solved by adding
ActiveSheet.ScrollArea = "A1:H19"
into the VBA. Thanks a lot.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900