Print PDF VBA

[agrawaltanu21]

I have made VBA code to print PDF any excel sheet. But i am running this code, i am getting one extra page that is blank, can you please help to rectify this problem.
Sub Macro1()
'

Dim lRow As Long
Dim lCol As Long
Dim sht As Worksheet
Dim StartCell As Range

'
Sheets("Sheet1").Select
Worksheets("Sheet1").UsedRange

On Error Resume Next
lRow = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0

MsgBox "Last Row: " & lRow


On Error Resume Next
lCol = Cells.Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0

'MsgBox "Last Column: " & lCol


Set StartCell = Range("A1")

'MsgBox "Last Row: " & lRow
'ActiveSheet.Range("A1:F8").Select

ActiveSheet.Range("A1", ActiveSheet.Cells(lRow, lCol)).Select

ActiveSheet.PageSetup.PrintArea = Range("A1", Cells(lRow, lCol)).Address
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
'ActiveSheet.PageSetup.PrintArea = "$A$1:$AN$100"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.7)
.RightMargin = Application.InchesToPoints(0.7)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 90
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\\Downloads\credits\Automation\Formula.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub

 

[Alex Blakenburg]

I am running the macro and it works fine for me.
What is the full range that you are printing ?
From what cell to what cell ? I will try and replicate the same range.

If I still can cause it to happen then, I won't be able to do it without a copy of your workbook.

 

[agrawaltanu21]

I am running the macro and it works fine for me.
What is the full range that you are printing ?
From what cell to what cell ? I will try and replicate the same range.

If I still can cause it to happen then, I won't be able to do it without a copy of your workbook.

Try Large Range ( A1:BZ600), fill random data in the cells and try to print.

 

[Alex Blakenburg]

I think it much to wide to for it to make sense outputting it to a pdf but on my Excel is still only produces a single page.

 

[agrawaltanu21]

I think it much to wide to for it to make sense outputting it to a pdf but on my Excel is still only produces a single page.

View attachment 45020

View attachment 45018

View attachment 45019

can you share ur MSTEAM id so i can see, because in my excel it is printing one extra blank sheet

 

[agrawaltanu21]

I think it much to wide to for it to make sense outputting it to a pdf but on my Excel is still only produces a single page.

View attachment 45020

View attachment 45018

View attachment 45019

 

[Alex Blakenburg]

Can you desensitize your workbook and put it as public in Dropbox or onedrive or Googledrive ?

 

[agrawaltanu21]

Can you desensitize your workbook and put it as public in Dropbox or onedrive or Googledrive ?

Book1.xlsm

drive.google.com

 

[Alex Blakenburg]

I am a bit surprised you haven't cut down your code to just the essential code I showed you previously.
However, it is not the cause of your additional page and on my machine it is still only producing 1 page.

A workaround might be to add a from and to page on your export to pdf function as per the below:-
(It would be interesting if you could run your version on a colleagues machine and see if it has the same issue because it looks like it might be specific to your machine)

Restricting the output to one page

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\ADMIN\Downloads\Formula.pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True, from:=1, to:=1

 

[agrawaltanu21]

I am a bit surprised you haven't cut down your code to just the essential code I showed you previously.
However, it is not the cause of your additional page and on my machine it is still only producing 1 page.

A workaround might be to add a from and to page on your export to pdf function as per the below:-
(It would be interesting if you could run your version on a colleagues machine and see if it has the same issue because it looks like it might be specific to your machine)

Restricting the output to one page

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "C:\Users\ADMIN\Downloads\Formula.pdf", Quality:= _
    xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True, from:=1, to:=1

i have cut down my code in the main file. the file above i have shared its woking fine with me means no blank pages, but when i run other files, its give me extra blank pages. I am trying to share that file with you so u can have better idea why blank page is coming
I have used from:=1, to:=1 but its give me only one page.

 

[agrawaltanu21]

I got the solution. The file i was Printing, cells having some notes and comments due to that extra blank page was showing now it is resolved. Thanks anyway