Print specified columns and until blank rows to pdf.

[austin397]

Hello all, I am looking to print to pdf from column A to Column R, starting at Row 2 and ending with the last cell to contain text or is formatted. I am new to VBA and havent been able to figure it out myself or understand examples of similar questions.

I would like it to take the width and fit to one page wide, while allowing the length to continue for however many pages it needs. The rows can be anywhere from Row 2 to Row 5 or from Row 2 to Row 700 and any number in between.

Any help would be appreciated, thank you!

 

[portews]

Try this:
I included a choose printer, not knowing what the users have for a printer driver.

Sub PrintMe()
Dim FinalRow As Long
Dim bOK As String
[COLOR=#00ff00]'select your sheet[/COLOR]
Sheets(1).Select
[COLOR=#00ff00]'find the last row used in column A[/COLOR]
FinalRow = Cells(Rows.Count, "A").End(xlUp).Row
[COLOR=#00ff00]'set printer options[/COLOR]
With ActiveSheet.PageSetup
    .PrintTitleRows = "$1:$1"
    .FitToPagesWide = 1
    .PrintArea = "A2:R" & FinalRow
    .Orientation = xlLandscape
End With
[COLOR=#00ff00]'choose the printer[/COLOR]
bOK = Application.Dialogs(xlDialogPrinterSetup).Show
     If bOK = False Then Exit Sub
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

 

[austin397]

Thank you for your reply portnews!

This worked for the most part, but it is not fitting the width to one page. Otherwise it worked perfectly!

Also, it is going to use "Adobe PDF" printer driver.

 

[portews]

The problem I've seen is the full printer name has a port number after it (something like "on Ne01:") appended after the "Adobe PDF". In my case it's Ne05. Looking around, it can be other names (like "WS101" and this can be different from computer to computer. It seems like a real pain to determine on the fly. If you're going to use it on one machine, you can find the one yours is set for and hard code it. The number itself may change when you add a printer.

Here's utility code to help you find the full name. Select the printer and a box will pop up the full name. Copy that and put it in the code.

Sub testPrintQueue()
bOK = Application.Dialogs(xlDialogPrinterSetup).Show
     If bOK = False Then Exit Sub
x = InputBox("The active printer name is:" & vbNewLine & "(Copy and put into code)", "Printer Name", Application.ActivePrinter)
End Sub

Here's the revised code to make it all fit on one page and will go thru the port names, increments the number and hoping for a match.

Sub PrintMe()
Dim FinalRow As Integer
Dim C As Integer
Dim PrinterName As String
[COLOR=#00ff00]
[/COLOR]
[COLOR=#00ff00]'select your sheet[/COLOR]
Sheets(1).Select
[COLOR=#00ff00]'find the last row used in column A[/COLOR]
FinalRow = Cells(Rows.Count, "A").End(xlUp).Row
'set printer options
With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PrintArea = "A2:R" & FinalRow
    .PrintTitleRows = "$1:$1"
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
End With


C = 1
 
On Error GoTo MakePDFError:
     
ResumePrinting:
    If C < 10 Then
        PrinterName = "Adobe PDF on Ne0" & C & ":"
    Else
        PrinterName = "Adobe PDF on Ne" & C & ":"
    End If
     
    Application.ActivePrinter = PrinterName
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    PrinterName, collate:=True
     
Exit Sub


MakePDFError:
    C = C + 1
Resume ResumePrinting:


End Sub

 

[austin397]

Woohoo! That worked perfectly, thank you!