+ Reply to Thread
Results 1 to 2 of 2

Print Area

Hybrid View

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Brooklyn NY
    MS-Off Ver
    2010
    Posts
    1

    Print Area

    Hello all, I'm having a problem dynamically setting the print area:


        
        ActiveSheet.Range("A1:AG19").Select
        Selection.copy
        i = True
        lastRow = Range("a1").End(xlDown).row
        ranges = "$A$1:$AG$19,"
    
        While i = True
            If IsEmpty(Cells(lastRow + 2, 1)) Then
            'in theory at this point the macro should have found the
            'bottom most output
                Range("A" & lastRow + 2).Select
                ActiveSheet.Paste
                Application.CutCopyMode = False
                ranges = ranges & "$A" & "$" & (lastRow + 2) & ":" & "$AG" & "$" & (lastRow + 20)
                i = False
            Else
                ranges = ranges & "$A" & "$" & (lastRow + 2) & ":" & "$AG" & "$" & (lastRow + 20) & ","
                lastRow = Range("A" & lastRow + 2).End(xlDown).row
                
            End If
        Wend
    
        ActiveSheet.PageSetup.PrintArea = ranges
    Basically I start with a table that runs from A1 to AG19, and I copy it. Column A is placeholder column, which allows me to use the .End(xlDown) function to find the bottom of the table. I find the bottom of the table; and if a cell two below the bottom is filled, I know that a table has already been copied to that location. Then the function iterates, until it finds the lowest table that has been inserted into the worksheet.

    So for instance I start with a table for A1 to AG19; if I have already inserted a table that goes from A21 to AG39, the function will iterate until the next table that can be inserted runs from A41 to AG59.

    The ranges variable is an attempt to create a string with the exact range of the tables; so that if I have a table on A1 to AG19, and a table on A21 to AG 39, and a table from A41 to AG59, I will make the string

    "$A$1:$AG$19, $A$21:$AG$39, $A$41:$AG$59" which is the format, seemingly that the ActiveSheet.PageSetup.PrintArea command requires.

    However when I run this code, it returns error 1004.

    Is this an error with the variable type (meaning that the ActiveSheet.PageSetup.PrintArea doesn't accept strings in that specific format)?
    Is this an error with the string itself (meaning that $A$1:$AG$19, $A$21:$AG$39, $A$41:$AG$59 is not in the correct form?

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Print Area

    Excel doesn't support non-contiguous print areas, unfortunately, so I suspect that's your problem. Perhaps you could re-do your macro to copy the selected ranges (or values of) to a new sheet, in a contiguous range, then print that? I would like to suggest code to do that, but I'm very much a VBA learner and that's beyond my skill level - sorry.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Set print area of selected range then print preview if ok then print the document!
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2014, 07:10 AM
  2. Macro - Set Print Area for Changing Data Area
    By ksp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-27-2013, 04:19 AM
  3. Replies: 1
    Last Post: 02-12-2013, 12:16 PM
  4. VBA to set print range, open box asking for print qty, print, then redefine print area
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2012, 06:43 PM
  5. Replies: 8
    Last Post: 05-23-2007, 06:39 AM
  6. [SOLVED] How do you turn off a print area for a page? (no print area)
    By Grunen in forum Excel General
    Replies: 4
    Last Post: 10-08-2005, 03:05 PM
  7. Print Area:What would be the code for setting the print area
    By wammer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1