CODEWITHSUNDEEP
excelvba
Enthusiast
Enthusiast

Reputation: 1

Combine Multiple Ranges in one pdf

On the basis of a few posts I was able to make below script which prints several selected ranges to a pdf file. However, all ranges are printed on a seperate sheet.

Currently NewRng.Address="A1:G9,A13:G14,A18:G37". I think it might need to be "A1:G9;A13:G14;A18:G37" (seperated by ; instead of ,)(?)

Can someone explain how can I print the selected ranges on one sheet?

Thank you so much!

Script:

   Sub CreatePDF_Selection1()

    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim NewRng As Range

    With ThisWorkbook.Sheets("Sheet1")
        Set rng1 = .Range("A1:G9")
        Set rng2 = .Range("A13:G14")
        Set rng3 = .Range("A18:G37")
        
        Set NewRng = .Range(rng1.Address & "," & rng2.Address & "," & rng3.Address)

        Debug.Print NewRng.Address
    
    Sheets("Sheet1").Activate
    ActiveSheet.Range(NewRng.Address).Select
    
    Sheets(Array("Sheet1")).Select

   ThisWorkbook.Sheets(Array("Sheet1")).Select
   Selection.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="U:\Sample Excel File Saved As PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=True, _
    From:=1, _
    OpenAfterPublish:=True
    End With

   End Sub

Upvotes: 0

Views: 622

Answers (3)

RetiredGeek
RetiredGeek

Reputation: 3168

Rather than select various ranges just hide the rows you don't want to print then print the entire range.

Option Explicit

Sub CreatePDF_Selection1()
   
  Dim rng1 As Range

  ThisWorkbook.Sheets("Sheet1").Activate
  Set rng1 = Range("A1:G37")
     
  Range("A10:A12").EntireRow.Hidden = True  '*** Hide rows not to print ***
  Range("A15:A17").EntireRow.Hidden = True
            
  rng1.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:="U:\Sample Excel File Saved As PDF", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=False, _
    IgnorePrintAreas:=True, _
    From:=1, _
    OpenAfterPublish:=True

  Rows("1:37").EntireRow.Hidden = False '*** Unhide hidden rows ***

End Sub 'CreatePDF_Selection1()

HTH

Edit: Attach test output. enter image description here

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54983

Export Non-Contiguous Range to PDF

This solution uses the Application.Union method to create the range to be exported. The range is then copied using the Range.Copy method to a newly added worksheet and exported from there. Then the newly added worksheet is deleted.

Option Explicit

Sub CreatePDF_Selection1()
    
    Const FilePath As String = "U:\Sample Excel File Saved As PDF"
    Const SheetName As String = "Sheet1"
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    ' Define Copy Range.
    With wb.Worksheets(SheetName)
        Dim rng As Range
        Set rng = Union(.Range("A1:G9"), .Range("A13:G14"), .Range("A18:G37"))
    End With
    
    ' Copy Copy Range to new worksheet, export to PDF and delete new worksheet.
    With Worksheets.Add
        ' This will copy values and formats.
        rng.Copy .Range("A1")
        .ExportAsFixedFormat Type:=xlTypePDF, _
                             Filename:=FilePath, _
                             Quality:=xlQualityStandard, _
                             IncludeDocProperties:=False, _
                             IgnorePrintAreas:=True, _
                             OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
End Sub

Upvotes: 0

Viktor West
Viktor West

Reputation: 574

I could not find a direct solution, so there is a work about here. A new worksheet will be added. The content will be copied there as a continuous range. The sheet will be exported as PDF, than the not needed sheet will be deleted.

Sub CreatePDF_Selection1()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim NewRng As Range
    
    Application.ScreenUpdating = False
    With Sheet1
        Set rng1 = .Range("A1:G9")
        Set rng2 = .Range("A13:G14")
        Set rng3 = .Range("A18:G37")
        Set NewRng = Union(rng1, rng2, rng3)
    End With
    
    'Creating test values
    rng1.Value = "Test 1"
    rng2.Value = "Test 2"
    rng3.Value = "Test 3"
    
    NewRng.Copy
    
    'adding a new sheet
    Worksheets.Add after:=Sheet1
    With ActiveSheet
        .Paste
        .ExportAsFixedFormat,  _
          Type:=xlTypePDF, _
          Filename:="U:\Sample Excel File Saved As PDF", _
          Quality:=xlQualityStandard, _
          IncludeDocProperties:=False, _
          IgnorePrintAreas:=True, _
          From:=1, _
          OpenAfterPublish:=True
        Application.DisplayAlerts = False
        .Delete 'delete the unwanted worksheet
        Application.DisplayAlerts = True
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Related Questions