[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Excel 2007 - Setting print areas

Posted on 2011-05-02
5
Medium Priority
?
264 Views
Last Modified: 2012-05-11
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:

Sub MyPrint()
    CurPrtArea = ActiveSheet.PageSetup.PrintArea
    If Range("linkcellfromCBox") = True Then
        myPrtArea = "B101:D131"
        With ActiveSheet.PageSetup
            .PageSetup.PrintArea = myPrtArea
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintOut
        End With
    End If
 '
 '~~> And So On
 '
End Sub

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.

Allowances                                               FF4718:FR5695
Annual Leave                                          FV5926:GJ6100
Calculation of Commission                       LQ7200:LV7240
Calculation of Superannuation                    LW7250:MD7300
Classification Summary                  DF3204:DR3400
Comments                                                ED3615:EG3700
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
Methodology                  CZ3000:DC3100
Other Conditions                  FS5772:FU5900
Parental Leave                  HA6400:HT6600
Payrate Chronology                DV3511:EC3600
Penalty Summary                   EH3718:FE4695
Personal Leave                   GK6155:GZ6395
PILN                        IN6841:JB6900
Public Holidays                   DS3408:DU3500
Redundancy                   JC6940:JQ700

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.
0
Comment
Question by:Andross9
  • 3
  • 2
5 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35511262
Dear Andross,

it seems to me you have to loop from your  myPrtArea = "B101:D131" the B101 down until b131 untilyou find nothing in your cell. Then assign your counter to the end of you statement like this  myPrtArea = "B101:D" & Counter


rest stay the same


Kind regards

Eric
0
 

Author Comment

by:Andross9
ID: 35511674
Thanks Zwiekhorst:,

Not too confident with loops,

Could you give me an example of what it may look like,

Appreciate the help.

Ross
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 2000 total points
ID: 35511892
Dear andross

Sub MyPrint()
Dim i As Integer
i=101
While Cells(i, 0) > "" 'while will loop until condition is met..
i = i + 1
Wend  'wend is while end, end of loop everything between while and wend is executed
i=i-1
 CurPrtArea = ActiveSheet.PageSetup.PrintArea
    If Range("linkcellfromCBox") = True Then
        myPrtArea = "B101:D" & i
        With ActiveSheet.PageSetup
            .PageSetup.PrintArea = myPrtArea
            .FitToPagesWide = 1
            .FitToPagesTall = 1
            .PrintOut
        End With
    End If
 '
 '~~> And So On
 '

End Sub
at the end of the wend I hold the first line where column A starting from row 101 has no entry. if you need the last one holding a entry use i=i-1
then your code like in the example I made...

Kind regards
0
 

Author Closing Comment

by:Andross9
ID: 35511965
Thanks so much.

Appreciate the support.

Andross9
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35512182
No problem appriciate the points...
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question