Print Area Range Selection

[Emma Aumack]

I am trying to set the print area in a macro. I have varying lines of data
and the last column of data does not contain the last row of data. In the
code below, which I copied in a previous post from Ivan Raiminius, how do I
add 3 rows to my range so that the print area will include all my data?

Sub PrintArea()
'
' PrintArea Macro
' Macro recorded 11/20/2008 by Network Administrator
'

'
Dim i As Long
Dim j As Long
Dim rng As Range
Set rng = Range("a1..n1") ' the address of first row of data you want to
Print out
j = 0
For i = 1 To rng.Columns.Count
j = WorksheetFunction.Max(j, rng.Cells(Application.Rows.Count - rng.Row,
1).End(xlUp).Row)
Next i
ActiveSheet.PageSetup.PrintArea = rng.Resize(j - rng.Row + 1,
rng.Columns.Count).Address

End Sub

Thanks so much for your help..

 

[Susan]

i would say, change this:

ActiveSheet.PageSetup.PrintArea = rng.Resize(j - rng.Row + 1,
rng.Columns.Count).Address

to this

ActiveSheet.PageSetup.PrintArea = rng.Resize(j - rng.Row + 4,
rng.Columns.Count).Address

that would add 3 more rows.

susan

 

[Jim Rech]

I'd suggest a slightly different approach:

Sub SetPrintArea()
Dim Rng As Range
Dim LastCell As Range
Set Rng = Range("a1:n1") ' the address of first row of data you want to
Set LastCell = Rng.EntireColumn.Find("*", Rng.Cells(1), , , ,
xlPrevious)
Rng.Resize(LastCell.Row - Rng.Row + 1).Name = "Print_Area"
End Sub

--
Jim
|I am trying to set the print area in a macro. I have varying lines of data
| and the last column of data does not contain the last row of data. In the
| code below, which I copied in a previous post from Ivan Raiminius, how do
I
| add 3 rows to my range so that the print area will include all my data?
|
| Sub PrintArea()
| '
| ' PrintArea Macro
| ' Macro recorded 11/20/2008 by Network Administrator
| '
|
| '
| Dim i As Long
| Dim j As Long
| Dim rng As Range
| Set rng = Range("a1..n1") ' the address of first row of data you want to
| Print out
| j = 0
| For i = 1 To rng.Columns.Count
| j = WorksheetFunction.Max(j, rng.Cells(Application.Rows.Count - rng.Row,
| 1).End(xlUp).Row)
| Next i
| ActiveSheet.PageSetup.PrintArea = rng.Resize(j - rng.Row + 1,
| rng.Columns.Count).Address
|
| End Sub
|
| Thanks so much for your help..
|
| --
| www.bardpv.com
| Tempe, Arizona