Why does Excel print the whole worksheet?
 

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

Select the worksheet range that you want to print, and click on
File | Print Area | Set Print Area.

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

Problem with that is, it has to be set again each time i add new data, which
i do a lot?
anyway for it to be done automatically?

"garfield-n-odie" wrote:

Select the worksheet range that you want to print, and click on
File | Print Area | Set Print Area.

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

Not that I know of, but wait around a bit and someone else here
might chime in. I don't think Excel will automatically know the
difference between actual data and hidden formuli on a worksheet.
You might try restructuring the spreadsheet so that the data
are on one sheet, and the hidden formuli on another sheet, and
then Excel should know where the end of the data sheet is.

Stuart wrote:

Problem with that is, it has to be set again each time i add new data, which
i do a lot?
anyway for it to be done automatically?

"garfield-n-odie" wrote:


Select the worksheet range that you want to print, and click on
File | Print Area | Set Print Area.

Stuart wrote:


I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

--

Dave Peterson

I have tried that but unfortunatly it doesnt change anything. Garfield
suggested restructuring the spreadsheet. how do i do what he suggests?

"Dave Peterson" wrote:

Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

--

Dave Peterson

Maybe Garfield will reply to what he meant.

But what do Hidden formulas mean?

I assumed they evaluated to "" if a cell is empty.

=if(a23232="","",vlookup(...))

If I'm close, are you sure your formulas evaluate to "" (not " "--with that
extra space character).

----
This technique has worked for me in similar situations.

Stuart wrote:

I have tried that but unfortunatly it doesnt change anything. Garfield
suggested restructuring the spreadsheet. how do i do what he suggests?

"Dave Peterson" wrote:

Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

--

Dave Peterson


--

Dave Peterson

Having never seen your Excel file, and not knowing what your
hidden formuli do or why they are hidden to begin with, I can't
offer a specific suggestion as to how you might go about
restructuring your Excel file. But I was thinking that there
might be a way for you to put all of the data on one sheet, and
to put all of the formuli on another sheet. Then you can add new
data to the data sheet as often as you wish, and Excel will know
what the last used cell is on the data sheet when you go to print
the data.

Stuart wrote:
I have tried that but unfortunatly it doesnt change anything. Garfield
suggested restructuring the spreadsheet. how do i do what he suggests?

"Dave Peterson" wrote:


Can you pick out a column that always has data if that row is used?

If you can, then maybe this old post will help you (I used column A):

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$A$1:$A$1000<""),ROW(Sheet1!$A$1:$A$1000) )

(Make that 1000 big enough to extend past the last possible row.)

Then once mo
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,3)

That last 3 represents the last column to print (A:C in my example).

And change the worksheet (sheet1) if necessary (in all the places).

Stuart wrote:

I have an Excel worksheet that when i print preivew indicates me printing the
whole lot. i have hidden formuli that run right to the bottom of the sheet,
but i only want to print up to the row that has any actual data in. how do i
do this?

--

Dave Peterson