select and print range of rows

092202 · Jul 4th 2005

I was hoping that someone might know how to add pop up windows in excel via a macro in order to
- prompt the user to enter number of the first row in the range
- prompt the user to enter number of the last row in the range
- prompt the user to enter number of printed copies
- print previously defined range of rows (or just one) on printer (label printer) in number of copies as defined before

For example, I am using a workbook to print labels (each row is the label), and when I wish to print from row 5 to row 8, I would like a macro to prompt me to enter number 5 as the first row in the range, and then number 8 as last row in the range, and then specify how many times I want to print out each row (label).
Thank you very much for your attention to this thread, may be you could help me.

dangelor · Jul 4th 2005

Re: select and print range of rows

Hello 092022,

Your problem is not too hard... take a look at the help files for the Inputbox function, the PrintArea property, and the Printout method.

092202 · Jul 4th 2005

Re: select and print range of rows

Quote from dangelor

Hello 092022,

Your problem is not too hard... take a look at the help files for the Inputbox function, the PrintArea property, and the Printout method.

Thanks, dangelor
I've done this macro, but , after selecting rows, HOW TO PRINT OUT THIS RANGE FROM a TO b??

Sub test01()
Dim a As Long, response As Long
a = Application.InputBox( _
Prompt:="Enter the first Rownumber", _
Title:="Select first rownumber:", Type:=1)
b = Application.InputBox( _
Prompt:="Enter the last Rownumber", _
Title:="Select last rownumber:", Type:=1)
Rows(a).Select
' HOW TO PRINT OUT THIS RANGE FROM a TO b??
End Sub

Brandtrock · Jul 4th 2005

Re: select and print range of rows

Welcome to the Forum!!!

Code

Option Explicit
Dim Times As String
Dim StartRow As String
Dim FinRow As String


Sub PrintRows()


    StartRow = InputBox("Enter the row to begin printing at: ", "Starting Row", 2)
    FinRow = InputBox("Enter the row to end printing at: ", "Finishing Row", 3)
    Times = InputBox("Enter the number of copies to print: ", "Copies to Print", 1)


    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
    End With
    
    Rows(StartRow & ":" & FinRow).Select
    Selection.PrintOut Copies:=Times, Collate:=True
End Sub

Display More

This code will do what you asked. I have left the input boxes for the start row and finish row with default values in them, eliminate them if you desire.

HTH,

092202 · Jul 4th 2005

Re: select and print range of rows

Thank you, Brandtrock
Using this code as template (below), I actually want to print each row from "test file.xls" (attached) separately (each one on individual continues label). However, before printing, I have to change printing output for each row: only data from first 5 sells should be printed out, each cell on individual line, like in "excel output file.xls" that I've attached here.
I provided fonts in cell’s comments. Actually, fonts not so important, I can change fonts later if they do not fit into the label.
Any suggestions how to improve this macro to achieve individual labels printed as I want in "excel output file.xls"?
Thank you

Sub PrintRows()
Dim Times As String
Dim StartRow As String
Dim FinRow As String
StartRow = InputBox("Enter the row to begin printing at: ", "Starting Row")
FinRow = InputBox("Enter the row to end printing at: ", "Finishing Row")
Times = InputBox("Enter the number of copies to print: ", "Copies to Print", 1)
Rows(StartRow & ":" & FinRow).Select
Selection.PrintOut Copies:=Times, Collate:=True
End Sub

dangelor · Jul 4th 2005

Re: select and print range of rows

Try this...

Code

Sub PrintLabels()
Dim x As Long
Dim y As Long
Dim z As Integer
Dim i As Integer
 
    Sheets("Sheet1").Activate
    x = InputBox("Enter the beginning row:")
    If Not IsNumeric(x) Or Not x > 1 Or Not x = Int(x) Then x = 2
    y = InputBox("Enter the ending row:")
    If Not IsNumeric(y) Or y < x Or Not y = Int(y) Then y = x
    z = InputBox("Enter the number of copies to print:")
    If Not IsNumeric(z) Or z < 0 Or Not z = Int(z) Then z = 1
    MsgBox "Ready to print " & z & " copies of rows " & x & " to " & y & "."
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
     
    On Error Resume Next
        Sheets("Label").Delete
    On Error GoTo 0
    Worksheets.Add
    With ActiveSheet
        .Name = "Label"
        .Range("a1") = "Batch:"
        .Range("a2") = "Date:"
        .Range("a3") = "QTY:"
        .Range("a5") = "Company:"
        .Cells.Font.Size = 22
        .Range("a4").Font.Size = 28
        .Range("b1").Font.Size = 36
        .Range("b3").Font.Size = 36
        .Range("a4").Font.Name = "arial black"
        .Range("b1:b5").Font.Name = "arial black"
        .Range("b2").NumberFormat = "m/d/yyyy"
        With .PageSetup
            .PrintArea = "$A$1:$B$5"
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .Orientation = xlLandscape
        End With
        
        For i = x To y
            .Range("b1") = Sheets("Sheet1").Cells(i, 1)
            .Range("b2") = Sheets("Sheet1").Cells(i, 2)
            .Range("b3") = Sheets("Sheet1").Cells(i, 3)
            .Range("a4") = Sheets("Sheet1").Cells(i, 4)
            .Range("b5") = Sheets("Sheet1").Cells(i, 5)
            .Range("a1:b5").Columns.AutoFit
            .PrintOut copies:=z
        Next i
    End With
    Sheets("Label").Delete
End Sub

Display More

092202 · Jul 5th 2005

Re: select and print range of rows

Thank you, dangelor, this last post is very close to what I want to achieve.
I still have to fiddle with correct fonts and printer setup, but at least now (with standard 12 size fonts) it prints out, but does not fit the label.
The biggest problem I have now that printout is too long (see attachment 123.pdf). Is it possible to decrease space (in macro) between 2 columns in printout? so it can be shorter. Also, is it possible to get rid of top and bottom margins in macro?
I understand that setting up correct driver settings for printer in Windows more important, but after playing with it still can't achieve right printout. That's why I am asking how to correct these settings in Excel macro.
Thanks

dangelor · Jul 5th 2005

Re: select and print range of rows

Try this, adjusting the column width as necessary...

Code

Sub PrintLabels()
    Dim x, y, z
    Dim i As Integer
     
    On Error Resume Next
    Sheets("Sheet1").Activate
    x = InputBox("Enter the beginning row:")
    If Not IsNumeric(x) Or Not x > 1 Or Not x = Int(x) Then x = 2
    y = InputBox("Enter the ending row:")
    If Not IsNumeric(y) Or y < x Or Not y = Int(y) Then y = x
    z = InputBox("Enter the number of copies to print:")
    If Not IsNumeric(z) Or z < 0 Or Not z = Int(z) Then z = 1
    MsgBox "Ready to print " & z & " copies of rows " & x & " to " & y & "."
     
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
     
    Sheets("Label").Delete
    On Error GoTo 0
    Worksheets.Add
    With ActiveSheet
        .Name = "Label"
        .Range("a1") = "Batch:"
        .Range("a2") = "Date:"
        .Range("a3") = "QTY:"
        .Range("a5") = "Company:"
        .Cells.Font.Size = 22
        .Range("a4").Font.Size = 28
        .Range("b1").Font.Size = 36
        .Range("b3").Font.Size = 36
        .Range("a4").Font.Name = "arial black"
        .Range("b1:b5").Font.Name = "arial black"
        .Range("b2").NumberFormat = "m/d/yyyy"
        With .PageSetup
            .PrintArea = "$A$1:$B$5"
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .Orientation = xlLandscape
        End With
        For i = x To y
            .Range("b1") = Sheets("Sheet1").Cells(i, 1)
            .Range("b2") = Sheets("Sheet1").Cells(i, 2)
            .Range("b3") = Sheets("Sheet1").Cells(i, 3)
            .Range("a4") = Sheets("Sheet1").Cells(i, 4)
            .Range("b5") = Sheets("Sheet1").Cells(i, 5)
            .Columns("A:A").ColumnWidth = 60 'adjust as needed
            .Columns("B:B").ColumnWidth = 25 'adjust as needed
            .PrintOut copies:=z
        Next i
    End With
    Sheets("Label").Delete
End Sub

Display More

092202 · Jul 5th 2005

Re: select and print range of rows

it's much more adjustable now, with ColumnWidth.
However, what I found, if A4 sell has got long product description, it does not fit into label (see example.pdf)
Is there any way to make A4 contents automatically go to the next line, if it's too long (A5 should move one line below also). In this case, if A4 is short, it will take only one line, and if it is longer then print area, it will take 2 lines.
Thanks

Werner · Jul 5th 2005

Re: select and print range of rows

Sorry to interfer in your conversation, but by reading this tread, it brought me a question in my mind. Is it possible to implement an menu option to choose the printer in this printing macro? If yes, any ideas how?

Werner

Werner · Jul 5th 2005

Re: select and print range of rows

Someone helped me out on my question. Here's the answer :

Code

Application.Dialogs(xlDialogPrinterSetup).Show

dangelor · Jul 6th 2005

Re: select and print range of rows

WrapText may work. Try...

Code

Sub PrintLabels()
    Dim x, y, z
    Dim i As Integer
     
    On Error Resume Next
    Sheets("Sheet1").Activate
    x = InputBox("Enter the beginning row:")
    If Not IsNumeric(x) Or Not x > 1 Or Not x = Int(x) Then x = 2
    y = InputBox("Enter the ending row:")
    If Not IsNumeric(y) Or y < x Or Not y = Int(y) Then y = x
    z = InputBox("Enter the number of copies to print:")
    If Not IsNumeric(z) Or z < 0 Or Not z = Int(z) Then z = 1
    MsgBox "Ready to print " & z & " copies of rows " & x & " to " & y & "."
     
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
     
    Sheets("Label").Delete
    On Error GoTo 0
    Worksheets.Add
    With ActiveSheet
        .Name = "Label"
        .Range("a1") = "Batch:"
        .Range("a2") = "Date:"
        .Range("a3") = "QTY:"
        .Range("a5") = "Company:"
        .Cells.Font.Size = 22
        .Range("a4").Font.Size = 28
        .Range("b1").Font.Size = 36
        .Range("b3").Font.Size = 36
        .Range("a4").Font.Name = "arial black"
        .Range("b1:b5").Font.Name = "arial black"
        .Range("b2").NumberFormat = "m/d/yyyy"


        With .PageSetup
            .PrintArea = "$A$1:$B$5"
            .LeftMargin = Application.InchesToPoints(0.25)
            .RightMargin = Application.InchesToPoints(0.25)
            .TopMargin = Application.InchesToPoints(0)
            .BottomMargin = Application.InchesToPoints(0)
            .Orientation = xlLandscape
        End With
        For i = x To y
            .Columns("A:A").ColumnWidth = 60 'adjust as needed
            .Columns("B:B").ColumnWidth = 25 'adjust as needed
            .Range("a4").WrapText = True
            .Range("b1") = Sheets("Sheet1").Cells(i, 1)
            .Range("b2") = Sheets("Sheet1").Cells(i, 2)
            .Range("b3") = Sheets("Sheet1").Cells(i, 3)
            .Range("a4") = Sheets("Sheet1").Cells(i, 4)
            .Range("b5") = Sheets("Sheet1").Cells(i, 5)
            .PrintOut copies:=z
        Next i
    End With
    Sheets("Label").Delete
End Sub

Display More

select and print range of rows

Participate now!

Share