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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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:
Solution

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
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;)
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
sorry but I note showing some empty cells shouldn't show may you fix it , please?
a.JPG
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752

ADVERTISEMENT

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
 

KalilMe

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

ADVERTISEMENT

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
 

KalilMe

Board Regular
Joined
Mar 5, 2021
Messages
53
Office Version
  1. 2016
Platform
  1. Windows
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 !
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,752
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,808
Members
416,983
Latest member
LessThanAverageUser

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