I have a sheet that reports on data in another worksheet based on
parameters in the third worksheet.

The number of lines on the report worksheet may vary according to the
parameters I put in the third (control) worksheet.

What I want to do is have a macro that will print the report but only
as far as the data is rather that the underlying formulae so I don't
have multiple wasted lines at the bottom.

Can anyone help me to do this please.

Thanks

David

You don't need a macro.

(saved from a previous post)

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

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

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

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

That last 5 represents the last column to print (A:E).

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



" wrote:

I have a sheet that reports on data in another worksheet based on
parameters in the third worksheet.

The number of lines on the report worksheet may vary according to the
parameters I put in the third (control) worksheet.

What I want to do is have a macro that will print the report but only
as far as the data is rather that the underlying formulae so I don't
have multiple wasted lines at the bottom.

Can anyone help me to do this please.

Thanks

David

--

Dave Peterson

Dave

I think I understand but would it be possible to give me the example
relative to the spreadsheet I am working on

The spreadsheet can be downloaded from
http://www.savefile.com/files/9285035

What I am trying to do is - from a button on the 'Control' worksheet
print the 'StudentPrintout' sheet and the Y9Form Printout but only for
the headings and the rows that the formulae have retreived data for and
not any other blank lines

Many thanks in anticipation.

David

Dave Peterson wrote:
You don't need a macro.

(saved from a previous post)

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

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

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

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

That last 5 represents the last column to print (A:E).

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



" wrote:

I have a sheet that reports on data in another worksheet based on
parameters in the third worksheet.

The number of lines on the report worksheet may vary according to the
parameters I put in the third (control) worksheet.

What I want to do is have a macro that will print the report but only
as far as the data is rather that the underlying formulae so I don't
have multiple wasted lines at the bottom.

Can anyone help me to do this please.

Thanks

David

--

Dave Peterson

I don't open attachments or download files.

Did you try that suggestion?

It should work if you use File|Print or if you just print the worksheet via your
macro.



" wrote:

Dave

I think I understand but would it be possible to give me the example
relative to the spreadsheet I am working on

The spreadsheet can be downloaded from
http://www.savefile.com/files/9285035

What I am trying to do is - from a button on the 'Control' worksheet
print the 'StudentPrintout' sheet and the Y9Form Printout but only for
the headings and the rows that the formulae have retreived data for and
not any other blank lines

Many thanks in anticipation.

David

Dave Peterson wrote:
You don't need a macro.

(saved from a previous post)

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

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

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

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

That last 5 represents the last column to print (A:E).

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



" wrote:

I have a sheet that reports on data in another worksheet based on
parameters in the third worksheet.

The number of lines on the report worksheet may vary according to the
parameters I put in the third (control) worksheet.

What I want to do is have a macro that will print the report but only
as far as the data is rather that the underlying formulae so I don't
have multiple wasted lines at the bottom.

Can anyone help me to do this please.

Thanks

David

--

Dave Peterson

--

Dave Peterson

I understand

Thanks - I'll try it

David


Dave Peterson wrote:
I don't open attachments or download files.

Did you try that suggestion?

It should work if you use File|Print or if you just print the worksheet via your
macro.



" wrote:

Dave

I think I understand but would it be possible to give me the example
relative to the spreadsheet I am working on

The spreadsheet can be downloaded from
http://www.savefile.com/files/9285035

What I am trying to do is - from a button on the 'Control' worksheet
print the 'StudentPrintout' sheet and the Y9Form Printout but only for
the headings and the rows that the formulae have retreived data for and
not any other blank lines

Many thanks in anticipation.

David

Dave Peterson wrote:
You don't need a macro.

(saved from a previous post)

I'm gonna use column A as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

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

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

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

That last 5 represents the last column to print (A:E).

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



" wrote:

I have a sheet that reports on data in another worksheet based on
parameters in the third worksheet.

The number of lines on the report worksheet may vary according to the
parameters I put in the third (control) worksheet.

What I want to do is have a macro that will print the report but only
as far as the data is rather that the underlying formulae so I don't
have multiple wasted lines at the bottom.

Can anyone help me to do this please.

Thanks

David

--

Dave Peterson

--

Dave Peterson