save as pdf without empty rows

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
hello
I search for a way to save my file as pdf without any empty rows but the empty rows it locates in this range A23:G34 so any empty rows are in this ranges should not show when save file as pdf
VBA Code:
Sub Save_PDF1()
  Dim Nm As String
  Dim Rng As Range
 
  Set Rng = Range("a1:g44")     '<- Set your range here
  Nm = ActiveWorkbook.FullName
  Nm = Left(Nm, InStrRev(Nm, ".") - 1) & Format(Now, " yyyymmddhhmmss") & ".pdf"
 
  Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Nm, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True
 
End Sub
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
Are you sure it doesn't print anything? If you click Yes it should print the PDF on the default printer.

I don't know how to show the print dialogue within Acrobat/Adobe.
without any doubt also I checked the setting the printer if there is outstanding the document not printing und still under procedure ,but there is no anything
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
Still not clear to me. I think you would have to copy the sheet to a temporary sheet and apply the formatting you want there and save/print that sheet as a PDF.
I knew it that's not clear I think it's possible by line code almost like this
VBA Code:
sh.Range("A23:E34").Borders.LineStyle = xlNone
but it should apply when ("item","describe" ,"model","ty" , "q") there is no data then should delete the borders
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
about post 12# I fixed it this is my mistake but may you help me about post11# ,please?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Here is the complete code using a different method to print the PDF. You can specify the Windows printer name or the default printer.
VBA Code:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If

Private Const SW_HIDE As Long = 0&


Public Sub Create_PDF_and_Print()

    Dim PDFfile As String
    Dim Rng As Range
    Dim hideRowsRange As Range, r As Long
    
    Set Rng = Range("A1:G44")     '<- Set your range here
    Set hideRowsRange = Range("A23:G34")
    
    'Hide empty rows
    
    For r = 1 To hideRowsRange.Rows.Count
        If Application.CountA(hideRowsRange.Rows(r)) = 0 Then hideRowsRange.Rows(r).EntireRow.Hidden = True
    Next
    
    'Create PDF
    
    PDFfile = ActiveWorkbook.FullName
    PDFfile = Left(PDFfile, InStrRev(PDFfile, ".") - 1) & Format(Now, " yyyymmddhhmmss") & ".pdf"
    Rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True
 
    'Unhide empty rows
    
    For r = 1 To hideRowsRange.Rows.Count
        If Application.CountA(hideRowsRange.Rows(r)) = 0 Then hideRowsRange.Rows(r).EntireRow.Hidden = False
    Next
    
    'Print PDF? Specify vbSystemModal to bring message prompt on top of other windows
    
    If MsgBox("Do you want to print " & PDFfile & "?", vbYesNo + vbSystemModal, "Print PDF") = vbYes Then
        ShellExecute_Print PDFfile 'print on default printer
        'ShellExecute_Print PDFfile, "Windows printer name" 'print on specific printer
    End If
    
End Sub


Private Sub ShellExecute_Print(file As String, Optional printerName As String)
    If printerName = "" Then
        ShellExecute Application.hwnd, "PrintTo", file, vbNullString, 0&, SW_HIDE
    Else
        ShellExecute Application.hwnd, "PrintTo", file, Chr(34) & printerName & Chr(34), 0&, SW_HIDE
    End If
End Sub
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

sorry I said that but it doesn't print any thing
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Try "Print" instead of "PrintTo" in the 2 ShellExecute lines.

Try a specific printer:
VBA Code:
ShellExecute_Print PDFfile, "Your Windows printer name"
I don't know why it isn't printing and can't really help you further.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752

ADVERTISEMENT

About the no printing problem. In File Explorer, right-click the .pdf file. Is Print one of the options in the context menu? If not, that might explain why the file isn't printing, because both methods are using the Windows shell (Context Menu).
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
About the no printing problem. In File Explorer, right-click the .pdf file. Is Print one of the options in the context menu? If not, that might explain why the file isn't printing, because both methods are using the Windows shell (Context Menu).
surly it's not existed in Context Menu
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
Can you reinstall Adobe Reader/Acrobat? That should restore the Print option to the context menu.

See:

 

Watch MrExcel Video

Forum statistics

Threads
1,129,682
Messages
5,637,764
Members
416,982
Latest member
lisam77

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