I am creating a spreadsheet, to be completed each month by many suppliers. In
order to make the sheets presentable, and ready to send to my own Client, I'd
like each sheet only to print rows that have data in column A. The sheet is
set for a maximum of 255 rows, but in some cases, may be s little as 10 rows.
How do I set the Print Area to automatically set to the number of rows for
each supplier?

I have tried using dynamic named ranges, but when I enter these names into
Page Setup, it works once, but when I go back to look at the Print Area, it
has then been fixed to the result of the named Range (i.e $A$5:$I$20), and no
longer referring to the named range.

Any help would be appreciated.

Saved from a previous post.

Can you use data|Filter|autofilter to show just the rows that don't appear
empty?

===
If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

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).

Huddle wrote:

I am creating a spreadsheet, to be completed each month by many suppliers. In
order to make the sheets presentable, and ready to send to my own Client, I'd
like each sheet only to print rows that have data in column A. The sheet is
set for a maximum of 255 rows, but in some cases, may be s little as 10 rows.
How do I set the Print Area to automatically set to the number of rows for
each supplier?

I have tried using dynamic named ranges, but when I enter these names into
Page Setup, it works once, but when I go back to look at the Print Area, it
has then been fixed to the result of the named Range (i.e $A$5:$I$20), and no
longer referring to the named range.

Any help would be appreciated.

--

Dave Peterson

Do it from the FILE menu
file
--
Don Guillett
SalesAid Software

"Huddle" wrote in message
...
I am creating a spreadsheet, to be completed each month by many suppliers.
In
order to make the sheets presentable, and ready to send to my own Client,
I'd
like each sheet only to print rows that have data in column A. The sheet
is
set for a maximum of 255 rows, but in some cases, may be s little as 10
rows.
How do I set the Print Area to automatically set to the number of rows for
each supplier?

I have tried using dynamic named ranges, but when I enter these names into
Page Setup, it works once, but when I go back to look at the Print Area,
it
has then been fixed to the result of the named Range (i.e $A$5:$I$20), and
no
longer referring to the named range.

Any help would be appreciated.

Dave,

This sheeet is to be used by some onon-Excel people, and I'm trying to
automate the print area, rather than filtering.

I had already tried using the post you copied, and found it very useful,
except that the OFFSET element seems to go wrong. The LOOKUP part does
accurately show which is the last row with data, but I'm having trouble
applying that to the Print Setup. I can operate the LOOUP within a cell, so I
can see the result, and check it works. I think the problem lies with the
OFFSET, which I can't test by entering into a cell, unless anyone knows
otherwise.

"Dave Peterson" wrote:

Saved from a previous post.

Can you use data|Filter|autofilter to show just the rows that don't appear
empty?

===
If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

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).

Huddle wrote:

I am creating a spreadsheet, to be completed each month by many suppliers. In
order to make the sheets presentable, and ready to send to my own Client, I'd
like each sheet only to print rows that have data in column A. The sheet is
set for a maximum of 255 rows, but in some cases, may be s little as 10 rows.
How do I set the Print Area to automatically set to the number of rows for
each supplier?

I have tried using dynamic named ranges, but when I enter these names into
Page Setup, it works once, but when I go back to look at the Print Area, it
has then been fixed to the result of the named Range (i.e $A$5:$I$20), and no
longer referring to the named range.

Any help would be appreciated.

--

Dave Peterson

After you use Insert|Name, you can use:
Edit|Goto and type:
Print_Area
and hit enter.

You should see that range get selected.

Huddle wrote:

Dave,

This sheeet is to be used by some onon-Excel people, and I'm trying to
automate the print area, rather than filtering.

I had already tried using the post you copied, and found it very useful,
except that the OFFSET element seems to go wrong. The LOOKUP part does
accurately show which is the last row with data, but I'm having trouble
applying that to the Print Setup. I can operate the LOOUP within a cell, so I
can see the result, and check it works. I think the problem lies with the
OFFSET, which I can't test by entering into a cell, unless anyone knows
otherwise.

"Dave Peterson" wrote:

Saved from a previous post.

Can you use data|Filter|autofilter to show just the rows that don't appear
empty?

===
If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

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).

Huddle wrote:

I am creating a spreadsheet, to be completed each month by many suppliers. In
order to make the sheets presentable, and ready to send to my own Client, I'd
like each sheet only to print rows that have data in column A. The sheet is
set for a maximum of 255 rows, but in some cases, may be s little as 10 rows.
How do I set the Print Area to automatically set to the number of rows for
each supplier?

I have tried using dynamic named ranges, but when I enter these names into
Page Setup, it works once, but when I go back to look at the Print Area, it
has then been fixed to the result of the named Range (i.e $A$5:$I$20), and no
longer referring to the named range.

Any help would be appreciated.

--

Dave Peterson


--

Dave Peterson

Dave,

Thanks. That worked, but once again, the next time I try to run it, the
print area stays as it was the first time, and when I look in Page Setup,
instead of the Name being in the print area field as I had entered it, I now
see =A1:C12. So it's worked out the number of rows I want to print, and then
fixed it. I need it to change depending on how much data is in the sheet at
that partcular time. That may go down as well as up.

Further to my past post, I checked the Name for Print Area, and instead of
the calc I enter, it now shows A1:C12 too. Which is the same as I see in the
Page Setup for Print Area.

I bet you went into page setup.

Stay out of that dialog--or reapply the name afterward.



Huddle wrote:

Further to my past post, I checked the Name for Print Area, and instead of
the calc I enter, it now shows A1:C12 too. Which is the same as I see in the
Page Setup for Print Area.

--

Dave Peterson