MS Excel 2007 - Setting print areas
Posted on 2011-05-02
I currently have a series of command buttons that initiate the printing of print areas within a single worksheet.
The current approach taken is shown below:
CurPrtArea = ActiveSheet.PageSetup.PrintArea
If Range("linkcellfromCBox") = True Then
myPrtArea = "B101:D131"
.PageSetup.PrintArea = myPrtArea
.FitToPagesWide = 1
.FitToPagesTall = 1
'~~> And So On
The print area is defined by the page width with all bar one needing to print to one A4 page in width BUT all need to be dynamic in determination of their length.
Annual Leave FV5926:GJ6100
Calculation of Commission LQ7200:LV7240
Calculation of Superannuation LW7250:MD7300
Classification Summary DF3204:DR3400
Coverage Summary DD3103:DE3200
Data entry A12:AB2950
Disaggregation of Annual and Personal Leave from Base Rate KQ7120:LP7195
Disaggregation of Super from Base Rate JR7039:KP7100
Long Service Leave HU6622:IM6800
Other Conditions FS5772:FU5900
Parental Leave HA6400:HT6600
Payrate Chronology DV3511:EC3600
Penalty Summary EH3718:FE4695
Personal Leave GK6155:GZ6395
Public Holidays DS3408:DU3500
Many of the print areas have large numbers of rows which are reserved for possible data to be inserted.
All bar one of the print areas are to be set at one page wide.
The length of the print will be data dependant based on the length of the wage assessment period and the industrial instrument being used.
The columns are static
I am hoping that there is a way to have excel automatically set the print area by recognising if there is any data in the first row of the first column of the print area.
If there is no data in the cell then the print area finished at the previous row.
Is this possible?
Any suggestions would be gratefully accepted.