save as pdf without empty rows

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
343
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
This temporarily hides empty rows found in A23:G34 and creates the PDF.
VBA Code:
Public Sub Save_PDF1()

    Dim Nm 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")
   
    For r = 1 To hideRowsRange.Rows.Count
        If Application.CountA(hideRowsRange.Rows(r)) = 0 Then hideRowsRange.Rows(r).EntireRow.Hidden = True
    Next
   
    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

    For r = 1 To hideRowsRange.Rows.Count
        If Application.CountA(hideRowsRange.Rows(r)) = 0 Then hideRowsRange.Rows(r).EntireRow.Hidden = False
    Next

End Sub
 
Last edited:
Upvote 0
Solution
thanks you've solved my question Just I have a question if it's possible can you add pop message after open file he asks me if I would print out the file if press ok then print and if press no then cancel;)
 
Upvote 0
sorry but I note showing some empty cells shouldn't show may you fix it , please?
a.JPG
 
Upvote 0
Just I have a question if it's possible can you add pop message after open file he asks me if I would print out the file if press ok then print and if press no then cancel
Insert this code:
VBA Code:
    If MsgBox("Do you want to print " & Nm & "?", vbYesNo + vbSystemModal, "Print PDF") = vbYes Then
        CreateObject("Shell.Application").Namespace(left(Nm, InStrRev(Nm, "\"))).Items.Item(Mid(Nm, InStrRev(Nm, "\") + 1)).InvokeVerb "Print"
    End If
 
Upvote 0
I no know how explain you but under item the range from A :E there are cells contain formatting and borders it should delete them or make them no filling borders
 
Upvote 0
Insert this code:
VBA Code:
    If MsgBox("Do you want to print " & Nm & "?", vbYesNo + vbSystemModal, "Print PDF") = vbYes Then
        CreateObject("Shell.Application").Namespace(left(Nm, InStrRev(Nm, "\"))).Items.Item(Mid(Nm, InStrRev(Nm, "\") + 1)).InvokeVerb "Print"
    End If
about this it doesn't print any thing when I press yes it does not show the printer dialog when print the page !
 
Upvote 0
about this it doesn't print any thing when I press yes it does not show the printer dialog when print the page !
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.
 
Upvote 0
I no know how explain you but under item the range from A :E there are cells contain formatting and borders it should delete them or make them no filling borders
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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