Setting print area with vba
 

Hi !

I would like to run vba code (to execute from a command button) that would
determine the last entry in column A of the acitve worksheet and then set
the print area of the active worksheet to that last row (entry) in column
A..and stop at col J going across.
The last column I would ever want to include in the print area is column
J...The rows could go on and on...or stop at 20.

For Example ... if the last entry in column A is cell A320, then the print
area would be A1:J320
(col J would always be the stop point for going across).

Thanks in advance for your help!
Kimberly

Setting print area with vba
 

Hi KimberlyC,

This this:

Private Sub CommandButton1_Click()
ThisWorkbook.ActiveSheet.PageSetup.PrintArea = "A1:J" &
LastInColumn(Range("A1"))

''get rid of this after testing
MsgBox "Print area set to " &
ThisWorkbook.ActiveSheet.PageSetup.PrintArea
End Sub


Function LastInColumn(rngInput As Range)
''Courtesy of http://www.j-walk.com, though a tad changed
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LastInColumn = WorkRange(i).Row
Exit Function
End If
Next i
End Function

Ray at work

"KimberlyC" wrote in message
...
Hi !

I would like to run vba code (to execute from a command button) that would
determine the last entry in column A of the acitve worksheet and then set
the print area of the active worksheet to that last row (entry) in column
A..and stop at col J going across.
The last column I would ever want to include in the print area is column
J...The rows could go on and on...or stop at 20.

For Example ... if the last entry in column A is cell A320, then the print
area would be A1:J320
(col J would always be the stop point for going across).

Thanks in advance for your help!
Kimberly

Setting print area with vba
 

This might be a simpler method. I always like to start at the bottom
of the worksheet and work up, in case of blank cells within the data
section I want to define. -Glenn

Sub SetPrintArea()
Range("A65536").Select
Selection.End(xlUp).Select
intLastRow = ActiveCell.Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$" & intLastRow
End Sub

Setting print area with vba
 

Thank you Ray and Glenn!!

Both ways worked great!!


"KimberlyC" wrote in message
...
Hi !

I would like to run vba code (to execute from a command button) that would
determine the last entry in column A of the acitve worksheet and then set
the print area of the active worksheet to that last row (entry) in column
A..and stop at col J going across.
The last column I would ever want to include in the print area is column
J...The rows could go on and on...or stop at 20.

For Example ... if the last entry in column A is cell A320, then the print
area would be A1:J320
(col J would always be the stop point for going across).

Thanks in advance for your help!
Kimberly