Print only range of cells that include data, even if all cells formatted

**liam_bettinson** · 07-30-2012, 07:40 AM

Hi there, I have been scouting the internet and can't seem to find an answer to my query.

I have designed a worksheet for our company to input project information. Information consists of a combination text, numbers and drop down lists (data validation). The sheet includes titles (that are set to print out on every page) in rows 1-15 and data is input through columns A-T. Quantity of information input can vary from 10 rows to 5000+, hence wanting a macro to auto adjust the print area. A number of my colleagues are not very familiar with Excel, therefore I do not want to rely on them having to adjust the print area manually. Data entry starts in row 16 and I have the print area set up as A1:T9999 (input would never exceed this even on the largest projects).

I have various formulas, protections, conditional formatting, data validation and general text & border formatting in cell range A16:T9999 and other formulas I have tried keep picking these up and therefore do not find 'blank' cells as the end of the print range.

My aim is to print only up to the first blank row found, ie, once a FULL blank row is found going down the sheet, this will equal the end of the print range. Either this, or I can set a formaula all the way down column U to look for a TRUE statement (formula would be arranged to find full blank row and return TRUE or FALSE - if this would work?).

Can anybody help me out with this? If I have missed any important information, please dont hesitate to ask.

Many thanks in advance.

Liam

---------- Post added at 12:40 PM ---------- Previous post was at 11:11 AM ----------

Can anybody help me out with this please? Woul really appreciate some tips

**benishiryo** · 07-30-2012, 08:08 AM

hi Liam, i dont think you need a macro for this. an OFFSET formula would be able to help you.
do you have a column that is definitely filled up (preferably numbers)? any row which is not filled means it should not be printed

if you have something i mentioned (maybe it's column A), you can use:
=OFFSET($A$1,0,0,COUNT(A1:A5000),20)

if your true/false is in column U, try:
=OFFSET($A$1,0,0,MATCH(TRUE,U1:U5000,0)-1,20)

Press CTRL + F3 to go to the Named Range. use either of the formula above to put inside PrintArea Named Range.

do upload a sample if it can't work.

**liam_bettinson** · 07-30-2012, 08:41 AM

Hi benishiryo, many thanks for your reply. I am unsure where to put this formula, do you mean in the page setup, print area box? I am unfamiliar with the Ctrl + F3. I have tried the top formula with no success. My data entry starts in row 16 and ends 9999 so would formula be? :

=OFFSET($B$16,0,0,COUNT(B16:B9999),20)

Just for my reference, what does the '20' at the end refer to?

**benishiryo** · 07-30-2012, 08:54 AM

using Excel 2007, you can go to the Formulas tab -> Name Manager. you should be able to see "Print Area"? Then below the screen, you should see "Refers to:". change that formula to the OFFSET formula.

yes you may refer it to until row 9999. 20 is the width of the range. which is A:T (20 columns)

**liam_bettinson** · 07-30-2012, 09:41 AM

Thanks for the info, unfortunately however this does not seem to work? When I go to print preview it is still trying to print the whole document? There is also now print area currently set. I assume the entire worksheet will be printed due to the formatting of all cells in the worksheet? How do I get around this, as these cells must be formatted/validated, yet I do not want to print all of the blank cells?