Print_Area Formula

[adamsm]

I’m using the following formula to set my print area as I add data rows to my worksheet.

=OFFSET('CustomerList'!$C$10,0,0,COUNTA('CustomerList'!$J:$J),8)

My column headers start from row 10. But before that I have the company information and other details starting from row 5 to row 8. Row 9 is empty.

The formula does not set the rows 5 to 8 in print area.

My question of concern is how to make the formula to include the print area starting from row 5 onwards and to get increased as I enter data to the sheet.


Any help on this would be kindly appreciated.


Thanks in advance.

[p45cal]

This formula is saying the print area's:
top left cell is always C10
top right cell is always J10
the number of cells down it goes is the number of non-empty cells there are in the ENTIRE column J.

So if column J only has anything at all in cell J1 and J2, the print area will be C10:J11 (two rows deep).
If you want to include row 5 then you must start at C5, not C10.
Instead of counting the number of non-blanks in the entire column J you could restrict that by only counting non-blank cells from J5 downwards. In the formula, that translates to changing:
('CustomerList'!$J:$J)
to:
('CustomerList'!$J5:$J1000)

I've used 1000 just as a big number, but if your data is more extensive, use a bigger number, or the row number of the bottommost cell in the sheet (65536 in pre xl2007, around a million otherwise).

[adamsm]

Thanks for the help p45cal

[mdmackillop]

or
=OFFSET(Sheet1!$C$5,0,0,COUNTA(Sheet1!$J:$J)+5,8)

[adamsm]

Thanks for the help mdmackillop. Your version seems to be simple and more understandable. The print area selected after writing your formula is from C5 to J10.

If I add a data to the row 11 and view “Print preview”; the preview does not show the area in the where the formula is applied.

What may be the reason for this?

I’m trying to make the formula to keep on selecting print area as data gets added to the rows.

I hope I have made my question clear.

[p45cal]

How are you using the result to set the print area?

[mdmackillop]

This is what I have.

[adamsm]

I'm sorry p45cal. I counts understand your question. If you don't mind could you explain it in detail? Is that you are asking how I set my print area?

[p45cal]

in your first post you said

I’m using the following formula to set my print area as I add data rows to my worksheet.

=OFFSET('CustomerList'!$C$10,0,0,COUNTA('CustomerList'!$J:$J),8)

Just how are you using it?

Where have you put this formula?

[adamsm]

I've attached the workbook for better understanding of my problem.

I have embedded a worksheet module code that would fill the columns "C" & "D" on data entry to column "E".

What I'm trying to get is the formula to keep selecting rows as I enter data rows.

I've designed the worksheet to look as like a white A4 paper. Meaning Im wanting the print are to start from row 5 and from column "C" to Column "K" onwards until the last data row.

[p45cal]

Try the attached. The print area named range had a workbook scope instead of a worksheet scope.

edit:I haven't changed the formula itself, so it depends on column K (not J), so adding comments will extend the print area.

[adamsm]

Thanks a lot p45cal & mdmackillop. Now the the print are gets selected according what I've asked.

Once again thanks for the help & I do really appreciate your help.