The Question:
Does the "PrintArea" Property require an A1 style reference?
The Problem:
I am using a macro to generate a report. I have the report macro handled. It generates a 4 column table that starts at A3 and ends at D42. Once row 42 is reached the macro starts at E3 and continues generating the table cycling everything it reaches row 42. Based on the predefined column widths in the sheet I can fit 16 columns on one printed sheet so if there are more than 5 tables in my report i need two pages. I have other things going on in the sheet that will be printed if I don't set the PrintArea.
The Code:
I use Option Base 1 for everything.
I have found that this works:
With Sheets("Report1")
col = "P"
.PageSetup.PrintArea = "A1:" & col & "42"
End With
The trouble with this method is I will have to use another routine to define "col" as the appropriate letters on intervals of 16, which is possible but more tedious.
This code returns a "PrintArea" region of "A1:P2":
With Sheets("Report1")
.Range(.Cells(1, 1), .Cells(42, 16)).Select
.PageSetup.PrintArea = Selection.CurrentRegion.Address
End With
I don't fully understand why the second method returns a region that is not the fully selected region.
Other Useful Info:
I mainly use fully updated Excel2003 on a fully updated WindowsXP machine. Occasionally I use fully updated Excel2007 on a fully updated Windows7 machine.
If someone knows of a more elegant way of setting the PrintArea I would greatly appreciate their help.
Thank you in advance for all of your help!!!
Curtis
Does the "PrintArea" Property require an A1 style reference?
The Problem:
I am using a macro to generate a report. I have the report macro handled. It generates a 4 column table that starts at A3 and ends at D42. Once row 42 is reached the macro starts at E3 and continues generating the table cycling everything it reaches row 42. Based on the predefined column widths in the sheet I can fit 16 columns on one printed sheet so if there are more than 5 tables in my report i need two pages. I have other things going on in the sheet that will be printed if I don't set the PrintArea.
The Code:
I use Option Base 1 for everything.
I have found that this works:
With Sheets("Report1")
col = "P"
.PageSetup.PrintArea = "A1:" & col & "42"
End With
The trouble with this method is I will have to use another routine to define "col" as the appropriate letters on intervals of 16, which is possible but more tedious.
This code returns a "PrintArea" region of "A1:P2":
With Sheets("Report1")
.Range(.Cells(1, 1), .Cells(42, 16)).Select
.PageSetup.PrintArea = Selection.CurrentRegion.Address
End With
I don't fully understand why the second method returns a region that is not the fully selected region.
Other Useful Info:
I mainly use fully updated Excel2003 on a fully updated WindowsXP machine. Occasionally I use fully updated Excel2007 on a fully updated Windows7 machine.
If someone knows of a more elegant way of setting the PrintArea I would greatly appreciate their help.
Thank you in advance for all of your help!!!
Curtis
