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]

After you run the macro and you are back in the spreadsheet.
If you hit ctrl+F3 it will put you into the names manager.
You will see a name called Print_Area, if there are several look under Scope for the sheet you are trying to print.
Does the range in Print_Area make sense ?
Are there blank rows included in that Print_Area (at the end) ?

 

[agrawaltanu21]

in the print area under name manager its giving ne the correct range without any black cell but in the pdf i m getting one blank page.

 

[Alex Blakenburg]

After you run the macro, switch to Page Break View and check if the bottom right corner is empty.

 

[Alex Blakenburg]

Try replacing the currently very large pagesetup lines with this:
(from PrintCom False to PrintCom True)
You will need to play with zoom figure so that the width fits.

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = 85
    End With
    Application.PrintCommunication = True

 

[agrawaltanu21]

Try replacing the currently very large pagesetup lines with this:
(from PrintCom False to PrintCom True)
You will need to play with zoom figure so that the width fits.

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = 85
    End With
    Application.PrintCommunication = True

I am still getting blank page

 

[Alex Blakenburg]

As per my Post #4 can you show me the bottom rows / page(s) in Page Break View ?

 

[agrawaltanu21]

Page break Review, and print area are same, can you run a same macro. My objective for this macro, to print EXCEL to PDF in landsacpe mode and fit on page sheet *( setting when we do print), so that entire area can be covered, Pages can be more than 1. U can also try that

 

[Alex Blakenburg]

Given that you want it as a single page can you try replacing that same piece of code with this.

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    Application.PrintCommunication = True

 

[agrawaltanu21]

I am tried this code also, i m getting one blank page in the end. if you run the macro, than it will help