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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Excel 2007 - setting printing area to dynamically recognise end of data.

I currently have a series of command buttons that initiate the printing of print areas within a single worksheet.

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.

The current code that i am having problems with is

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
 '
 '~~>

I have attached a file that I have been using.

The print area is B101:d131

Would appreciate some help in solving this issue.

Regards,

Andross9
 Setting-Print-Area.xlsm
0
Andross9
Asked:
Andross9
  • 5
  • 5
1 Solution
 
philip m o'brienCommented:
A couple of issues straight off:
The code
cells(i, 0)—this indicates row i and column 0. This must be at least column 1 i.e. cells(i, 1)
The code
With ActiveSheet.PageSetup .PageSetup.PrintArea = myPrtArea—this includes ".PageSetup.PageSetup" which will fail
 
0
 
Andross9Author Commented:
Thanks SubversiveArmadillo

the code

With ActiveSheet.PageSetup
            .PageSetup.PrintArea = myPrtArea

this includes ".PageSetup.PageSetup" which will fail

I am confused with this comment - can you expand?

What should it be?
0
 
philip m o'brienCommented:
Use:
 
With ActiveSheet.PageSetup
    .PrintArea = myPrtArea
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintOut
End With

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Andross9Author Commented:
Thanks again but still no luck.

Have tried in the file I sent to you and also in the attached file which has cbox

Have spent the last hour playing with changes to cide etc - finding context and practice of VBA hard to get my head around.

Can you help me with my less than satisfactory attempts

Andross9
0
 
Andross9Author Commented:
Help if I added the file updated-Printing-Area-Locations.xlsm
0
 
philip m o'brienCommented:
Final couple of points.
In your table you have entered b101"b131 in cell C3, which I assume is a typo. This mis-references and will fail.
The other point to bear in mind is that print areas need to be set at a worksheet level e.g. Sheet1!B101:B131 etc
So you have a few ways to handle this:
Include the sheet detail in the Print Area column; add an additional column to contain the sheet number and update your code to handle this; amend the code directly e.g. if they are all in sheet1 you could use:
 
myPrtArea = "Sheet1!" & cb.TopLeftCell.Offset(0, 2)

Open in new window

etc
Other than that there is nothing wrong with the code you now have.
Regards
0
 
Andross9Author Commented:
Thanks for your support,

Have made those suggested changes and still find that I get an error message

Object doesnt support this property or method

Thanks for trying to help here - might go back to the basic solution and print heaps of balnk pages.

Once again thanks for your efforts.

Andross9
0
 
philip m o'brienCommented:
No problem. Have you got details of where it's failing? I used your code and it was working for me.
0
 
Andross9Author Commented:
I have kept playing but this is where I am at.

The checkbox approach by itself works fine.

When I try to add in the dynamic print area setting - I have all the problems.

Regards,

Andross9
Frustrated-Printing-dynamically.xlsm
0
 
philip m o'brienCommented:
Andross,
I have double-checked your code, and except for the incorrect entry in cell C3 your macro is working fine. I have had it run through correctly from start to finish. Did you remember to change the value in that cell from B101"D131 to B101:D131, or try with a different selection?
That said this line seems to have no purpose unless it is used in another macro that you haven't included in your attachement:
CurPrtArea = Sheets("Sheet1").PageSetup.PrintArea

Open in new window

You can always amend slightly as below, but your code is valid. The only other possibility is if the problem comes up elsewhere in code you haven't provided.
 
Sub print_selection()
Dim cb As CheckBox
Dim descr As String
Dim myPrtArea As String

    For Each cb In Sheets("Sheet1").CheckBoxes
        If cb.Value = 1 Then
            descr = Trim(cb.TopLeftCell.Offset(0, 1))
            myPrtArea = "Sheet1!" & cb.TopLeftCell.Offset(0, 2)
            
            With Sheets("Sheet1")
                With .PageSetup
                    .PrintArea = myPrtArea
                    .FitToPagesWide = 1
                    .FitToPagesTall = 1
                End With
                .PrintOut
            End With
        End If
    Next cb
   
End Sub

Open in new window

Regards
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now