+ Reply to Thread
Results 1 to 4 of 4

Print a variable range based on cell reference

  1. #1
    Registered User
    Join Date
    12-29-2011
    Location
    Saskatoon, Saskatchewan
    MS-Off Ver
    Excel 2007
    Posts
    2

    Print a variable range based on cell reference

    I am trying to print out tags for use in our factory. I have it set up so that formulas take each value needed to complete each tag, so there is formulas for up to 500 tags. When I go to print these tags each time I need to select the page #s I want to print to prevent it from printing the blank cells. I have all of my formula's set up to output "" if there is no value. I have a cell that outputs the range needed as a text string, but no matter how I try to input it into the page setup (using indirect) it always just locks in on the range I used to create the initial macro.

    Any solution to this would be much appretiated I think ive been siting at my desk for 6 hours trying to figure this out...

    Adam Ondrusek
    Wilkinson Steel & Metals

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Print a variable range based on cell reference

    The only way to get a precise answer is to upload a sample workbook (exact structure but using dummy data) to provide the context to help with the solution.
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.

  3. #3
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Print a variable range based on cell reference

    Hi Adam

    Are you able to post some sample data?

    My initial thoughts would be:
    A macro to either set the print range (perhaps using a Dynamic Named Range as the source) or temporarily hide the empty cells.

    The macro could be a Workbook_BeforePrint and would fire when Print is pressed.

    Again, without having seen your spreadsheet, this might work (If your range needed is in cell A1 on Sheet1):
    Please Login or Register  to view this content.
    This should be entered in to the Worksheet macros in the VBE Editor (i.e. not in a Module or Sheet).

    If this doesn't work, please upload some sample data and we can probably solve this quite quickly.

    Cheers, Rob.

  4. #4
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Print a variable range based on cell reference

    PS - another option is to defin ehte PrintArea as a Dynamic Named Range

    From the menu, select Insert, Name, PrintArea enter an Offset command:
    Please Login or Register  to view this content.
    If PrintArea isn't there, just enter it as a new named range.

    This formula starts the PrintArea at A1, then expands it to the number of rows resulting from the COUNTIF statement (with 3 added for a few blank rows, etc at the top). The number of columns to print is 10.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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