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

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.

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.