Excel Printing Question 1

[MKW]

Anyone know of a way to only print rows in a spreadsheet that have a value in a certain column? What I have is an estimating spreadsheet that is four pages long. The columns are from left to right: QTY, DESCRIPTION, PRICE, EXTENSION. What I would like to do is not have the rows printed that have nothing in the QTY column, trying to save a few trees . Any ideas about a macro or something that would let me do this.

 

[SimonDavis]

Is it feasible to first sort the data so that the empty columns are at the end, then have the range up to where the empty columns start selected to print?

Codewise that should be simple enough, does it work for you?

 

[MKW]

Any thing along those lines would work. Some of the jobs we estimate only have 3 or 4 items, so there is no need to print out 150 rows parts that wont get used

 

[SimonDavis]

OK. Do you know anything about vbcode? A recorded macro won't work on 'moving targets'.

I would do the following;

- Set a range name for ALL the data (i.e. 4c x 150r) and give it the name 'alldata' or something. If you extend the range slightly beyong the last entry this will allow you to add items without them falling outside of the range.

- set an action to sort the data based on the qty column, so that any empty ones go to the bottom. You can set a secondary sort criteria to sort the data you want to print, such as description.

- When it's sorted, start at the top left cell, select 'end' 'down', which will take you to the last cell with data, then offset (in this case 3) to the last column.

- The range you now have highlighted is then printed.

I'm assuming you're up to speed on coding etc. If not I guess this sounds a bit complicated, but just me know and I'll knock up a prototype for you - it's pretty straighforward.

 

[MKW]

Thanks that makes a lot more sense now. Thank you for the help.