auto print range
Posted by bgoetz on August 09, 2001 5:12 PM
I have several workbooks designed for people who have little or no Excel experience. One of the features I would like to add is automatic settting of the correct print range for a variable length form. There are about 1000 rows available for data entry. All rows contain columns with formulas as well as free text fields, so the basic size of the sheet is constant. The amount of rows with entries in the text fields should be the factor that determines the "length" of the print range.
Posted by John on August 10, 2001 7:52 AM
This code should (should) work, with the proper changes noted in the comments. It could be called from a commandbutton on the worksheet, or from a custom menu.
Hope this helps
-John
Sub print_it()
' format screen to print properly
Dim last_row_used As Integer
last_row_used = 1
' replace the "A" with a column in which the user is inputting data
Do While Len(Range("A" & last_row_used).Value) <> 0
last_row_used = last_row_used + 1
Loop
' replace the "N" with your last column
activeworksheet.PageSetup.PrintArea = "$A$1:$N$" & last_row_used
activeworksheet.printout
End Sub
Posted by Ivan F Moala on August 10, 2001 1:30 PM
OR....incase the user uses the Print options
in excel......use Johns code BUT place it in the
Workbook beforeprint event so you will always
capture the printarea.
eg
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim last_row_used As Integer
With ActiveSheet
' format screen to print properly
last_row_used = 1
' replace the "A" with a column in which the user is inputting data
Do While Len(.Range("A" & last_row_used).Value) <> 0
last_row_used = last_row_used + 1
Loop
' replace the "N" with your last column
.PageSetup.PrintArea = "$A$1:$N$" & last_row_used
'activeworksheet.PrintOut
End With
End Sub
