Set Print Area in Excel using VBA 2

[cariengon]

I am trying to set the print area for a spreadsheet and for some reason it will not take my PrintArea setting. I don't want to set anything using .PageSetup except for Print Area. This is because I don't want my code too long or bulky. I'm using the following:

Sub Print_Area_Test()

Sheets("AP Due&quot.Select

'Find Grand Total row
For i = 5 To 65536 Step 1
test4 = Cells(i, 1)
If test4 = "Grand Total" Then
Grand_Tot_row = i
Exit For
End If
Next i

'Find last subtotal column
For i = 1 To 255 Step 1
test2 = Cells(5, i)
If test2 = "Total" Then
Last_col = i
Exit For
End If
Next i

Sheets("AP Due&quot.Activate
ActiveSheet.PageSetup.PrintArea = Range(Cells(6, 1), Cells(Grand_Tot_row, Last_col))

End Sub

The Basis of this array is to find the range. All of the other PageSetup options are set on the worksheet using File/Page Setup. The reason I need to find the Print Area is because the report can change in size.

Everything works, except for the ActiveSheet.PageSetup.PrintArea. The Cells in the Range return the correct range, so I'm a bit confused.

Thanks in advance,
Carie

 

[kylua]

I always do
Range("A1:Z21&quot.select
selection.print

But I got that off a recorded macro and there is almost certainly a way more elegant way of doing it.

 

[cariengon]

The problem is that the range is always changing - hence the array - to find the last column and row.

I just looked on MS Knowledgebase and it says that it's because there are no printer drivers set up - but they are...

Gotta love MS!

 

[rmikesmith]

Carie,

Don't believe everything MS tells you. The problem is the PrintArea property will only accept a string representation of a Range. So, using your example code, try this modification:


ActiveSheet.PageSetup.PrintArea = Range(Cells(6, 1), Cells(Grand_Tot_row, Last_col)).Address


HTH
Mike

 

[kylua]

You can adjust the Range("blah di blah&quot using a variable:

Set bottomrighthandcorner = Cells(Grand_Tot_row , Last_col )
brhc = bottomrighthandcorner.Address

Range("A1:" + brhc).Select

selection.print

 

[cariengon]

Thanks for all of your help. I got it to work right before I saw your .Address suggestions (which is what I used...)

'Set Print Area
' The Titles, Margins, Landscape, Legal - set on the sheet
Set mc = Range(Cells(6, 1), Cells(Grand_Tot_row, Last_col))
ActiveSheet.PageSetup.PrintArea = mc.Address()

We have 3 different solutions for the same thing!!

Thanks for your help!

Carie