Conditionally Change Print-Area

How Can I conditionally change Print-Area...something like Conditional
Formula?

Background:
I have designed a template for a class-room to update the student's profile.
I will distribute this template to every Class-Teacher S/he will the
distribute among students. Initially, I have setup 8 columns.

Problem I faced while printing is that suppose some class have 5 students
some have 8 students or some have 12. How can i automatically setup
print-area (include or exclude the unnecessary columns in print-area)

Hope I will get some solution for this.

Regards,
Murtaza



0
NoEmail9920 (149)
9/23/2004 8:55:29 AM
excel 39879 articles. 2 followers. Follow

5 Replies
522 Views

Similar Articles

[PageSpeed] 29

I had a similar problem to this and was advised to use the Autofilte
(Data, Filter, Autofilter) if you know how to work this you could, lik
me, filter for non-blank cells (perhaps on a column that will alway
have data in it - the NAME column for instance) then select the rang
and print it.  Each step can be recorded in a macro and you can assig
the macro to a button or keyboard shortcut.  Your final step of th
macro may need to include resetting the autofilter so it only print
your specific range but returns the worksheet back to showing blan
cells for further entry.

Hope this makes sense and helps with your problem.  Let me know if yo
need clarification on anything.

Fuzz

--
fuzzyfrea
-----------------------------------------------------------------------
fuzzyfreak's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1465
View this thread: http://www.excelforum.com/showthread.php?threadid=26272

0
9/23/2004 11:54:26 AM
If you set no print area then the entire sheet is always printed.  That may 
be a simple answer if that's the result you want.

If however you have some ranges you do not want to print you can create a 
dynamic Print_Area formula.  Here's an example.

Open the Define Name dialog and create or modify the name Print_Area to have 
this definition:

  =OFFSET(Sheet1!$A$1,0,0,10,COUNTA(Sheet1!$A$1:$J$1))

This will set a print area starting at A1 with 10 rows and as many columns 
as are used from A1 to J1 (assuming no empty columns).  So if you have 
entries in A1 and B1 only, the print area is A1:B10.  If you have entries in 
A1 through G1 then the print area is A1:G10, etc.

-- 
Jim Rech
Excel MVP
"Murtaza" <NoEmail@Please.com> wrote in message 
news:Ohi8VsUoEHA.2920@TK2MSFTNGP10.phx.gbl...
| How Can I conditionally change Print-Area...something like Conditional
| Formula?
|
| Background:
| I have designed a template for a class-room to update the student's 
profile.
| I will distribute this template to every Class-Teacher S/he will the
| distribute among students. Initially, I have setup 8 columns.
|
| Problem I faced while printing is that suppose some class have 5 students
| some have 8 students or some have 12. How can i automatically setup
| print-area (include or exclude the unnecessary columns in print-area)
|
| Hope I will get some solution for this.
|
| Regards,
| Murtaza
|
|
| 


0
jrrech (1933)
9/23/2004 12:02:32 PM
hi,
print macro.
Assuming that all your data will start at cell A1.

Sub macPrintOut
Range("A2").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="prntarea", RefersToR1C1:= _
Selection.CurrentRegion
ActiveSheet.PageSetup.PrintArea = "prntarea"
'ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _ 
Collate:=True
'ActiveWindow.View = xlNormalView
End sub
Add this as a publick sub. How?
copy the code above. in excel press Alt+F11.
in the left pane click this workbook.
in the right pane paste the code.
save file.

next put a run macro icon on your tool bar. how?
RClick the toolbar > click customize.
when the customize box comes up, in the left pane scroll
down to and click macro
click and drag the smiley face to the tool bar.
on the customize box click the modify selecton button.
At the bottom of the list click assign macro.
when the assign macro box comes up, click the macro
"prntarea". click ok.  done.

now when ever you click the smiley face, excel will select 
cell A2 and print all other data that is grouped to cell A2

>-----Original Message-----
>How Can I conditionally change Print-Area...something 
like Conditional
>Formula?
>
>Background:
>I have designed a template for a class-room to update the 
student's profile.
>I will distribute this template to every Class-Teacher 
S/he will the
>distribute among students. Initially, I have setup 8 
columns.
>
>Problem I faced while printing is that suppose some class 
have 5 students
>some have 8 students or some have 12. How can i 
automatically setup
>print-area (include or exclude the unnecessary columns in 
print-area)
>
>Hope I will get some solution for this.
>
>Regards,
>Murtaza
>
>
>
>.
>
0
anonymous (74718)
9/23/2004 12:20:25 PM
hi again,
forgot to mention. this will work on any sheet in the 
workbook.
:)

>-----Original Message-----
>hi,
>print macro.
>Assuming that all your data will start at cell A1.
>
>Sub macPrintOut
>Range("A2").Select
>Selection.CurrentRegion.Select
>ActiveWorkbook.Names.Add Name:="prntarea", RefersToR1C1:= 
_
>Selection.CurrentRegion
>ActiveSheet.PageSetup.PrintArea = "prntarea"
>'ActiveWindow.SelectedSheets.PrintPreview
>ActiveWindow.SelectedSheets.PrintOut Copies:=1, _ 
>Collate:=True
>'ActiveWindow.View = xlNormalView
>End sub
>Add this as a publick sub. How?
>copy the code above. in excel press Alt+F11.
>in the left pane click this workbook.
>in the right pane paste the code.
>save file.
>
>next put a run macro icon on your tool bar. how?
>RClick the toolbar > click customize.
>when the customize box comes up, in the left pane scroll
>down to and click macro
>click and drag the smiley face to the tool bar.
>on the customize box click the modify selecton button.
>At the bottom of the list click assign macro.
>when the assign macro box comes up, click the macro
>"prntarea". click ok.  done.
>
>now when ever you click the smiley face, excel will 
select 
>cell A2 and print all other data that is grouped to cell 
A2
>
>>-----Original Message-----
>>How Can I conditionally change Print-Area...something 
>like Conditional
>>Formula?
>>
>>Background:
>>I have designed a template for a class-room to update 
the 
>student's profile.
>>I will distribute this template to every Class-Teacher 
>S/he will the
>>distribute among students. Initially, I have setup 8 
>columns.
>>
>>Problem I faced while printing is that suppose some 
class 
>have 5 students
>>some have 8 students or some have 12. How can i 
>automatically setup
>>print-area (include or exclude the unnecessary columns 
in 
>print-area)
>>
>>Hope I will get some solution for this.
>>
>>Regards,
>>Murtaza
>>
>>
>>
>>.
>>
>.
>
0
anonymous (74718)
9/23/2004 12:41:05 PM
Thanks Jim:

That solve the problem very well.

Is there any work around to solve that If I 'CountA' returns greater than 10
then Page Orientation changes to Landscape?

Regards,

"Jim Rech" <jrrech@hotmail.com> wrote in message
news:entZnUWoEHA.2764@TK2MSFTNGP11.phx.gbl...
> If you set no print area then the entire sheet is always printed.  That
may
> be a simple answer if that's the result you want.
>
> If however you have some ranges you do not want to print you can create a
> dynamic Print_Area formula.  Here's an example.
>
> Open the Define Name dialog and create or modify the name Print_Area to
have
> this definition:
>
>   =OFFSET(Sheet1!$A$1,0,0,10,COUNTA(Sheet1!$A$1:$J$1))
>
> This will set a print area starting at A1 with 10 rows and as many columns
> as are used from A1 to J1 (assuming no empty columns).  So if you have
> entries in A1 and B1 only, the print area is A1:B10.  If you have entries
in
> A1 through G1 then the print area is A1:G10, etc.
>
> --
> Jim Rech
> Excel MVP
> "Murtaza" <NoEmail@Please.com> wrote in message
> news:Ohi8VsUoEHA.2920@TK2MSFTNGP10.phx.gbl...
> | How Can I conditionally change Print-Area...something like Conditional
> | Formula?
> |
> | Background:
> | I have designed a template for a class-room to update the student's
> profile.
> | I will distribute this template to every Class-Teacher S/he will the
> | distribute among students. Initially, I have setup 8 columns.
> |
> | Problem I faced while printing is that suppose some class have 5
students
> | some have 8 students or some have 12. How can i automatically setup
> | print-area (include or exclude the unnecessary columns in print-area)
> |
> | Hope I will get some solution for this.
> |
> | Regards,
> | Murtaza
> |
> |
> |
>
>


0
NoEmail9920 (149)
9/24/2004 6:16:18 AM
Reply:

Similar Artilces:

PO amount different on PO Entry screen from printed PO
Hi, We have a problem that seems to happen every month. In the Purchase Order Entry window, the subtotal on this one particular PO is $590,776.35. However if we print the PO, the subtotal amount on the printed PO is only $472,451.66. The only seems to happen on random PO's and we can't figure out why. It's starting to annoy our Purchasing department... Ideas? Thanks, --Nathan Nathan, What version and service pack of GP? Are there PO's entered manually or imported? Is the PO report modified? Are there typically a lot of changes to the PO's that this happens to?...

How to change cell color based on content
Hi, hope someone can help me. I don't use Excel much, but I'm creating a spreadsheet that keeps track of ticket sales for a charity I volunteer for. Anyway, what I want to do is to make the cells stand out for those that still owe money, either because a check is forthcoming or because they ordered by credit card. I want the credit card orders to show up with a different color (eg YELLOW) than those who are fully paid. I want the forthcoming check orders to show up in a different color (eg RED). So I have a column with payment type that is either Cash, Check, or Credit Card....

Multiple detail areas in one report
We are creating a database which uses several forms to input data into a Master_Data table. There are several categories for the data ex: Full Service Driver, Trade Driver, Misc Receipts, Misc Payouts. I need a report that breaks these categories out in detail like so: Cashier Driver# Driver Name Full Service Checks Full Service Currency Full Service Coin Subtotal Cashier Driver# Driver Name Trade Checks Trade Currency Trade Coin ...

I want to print multi pages on one page.
I've tried going into set up and under page layout telling it to print 2 pages per sheet, but that doesn't work. Is there another way? If, by "multi-pages" you really mean multiple worksheets on one page that is not possible. You could add a 'summary' worksheet to combin your data and print that with the "fit to page" option checked on th Page Setup screen. Be careful with the 'fit to screen' as it can mak the printout unreadably small. Good Luck Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p...

Time Zone Change
When I turned on my pc this morning I realized that my "adjust for daylight savings time" option was not checked off in outlook calendar options. However, when I check that box, it changes all my appointments by 1 hour. For example, I have an appointment for 11am and it moves it to 12pm. What can I do about this? Your help is very much appreciated. ...

Today Conditional Format
I have a column which calculated a date based on 3 working days from a date input into a diff column. What I want to do I have the calculated date appear bold if that date has passed based on the current date. For example if I input 01/08/11 the next column calculates 04/08/11 (3 working days). I want the caluclated date to appear bold when I open the spreadsheet on the 05/08/11 ro show the calculated date has passed. I can use conditional formatting but can't get the syntax right Regards Andy Win XP Pro Office 2010 Andy Roberts presented the following explanation : > I ha...

Employees who change passwords on my system?
How do I get my passwords set so eployee's quit hacking my site? From: UncIe Fester Cryptic Illusions wrote: > How do I get my passwords set so eployee's quit hacking my site? > > > > > > > > From: UncIe Fester Dear UncIe Fester, Have the 'eployees' come into the office and then cut their hands off. If they were physically present, they'd be 'inployees' and you'd have more granular control over their actions. MowGreen ================ * -343-* FDNY Never Forgotten ================ bant...

Printing Help
I know there should be an easy way to do this, but I have yet to figure it out. I have a 183 page report on school districts. I want to print all the information for one school district per page (or possibly two because a few of the larger districts might take up two pages.) On the report, I have one district per page or two and I could just print the whole thing but I don't want to. Is there a way to print a certain district? I don't want to just print page 42 and wonder what district that is. I want to print, let's say Spring School District, and only that page prints. Th...

when I open excel file,changes was undid-excel 2007, compatibility
I have a file that was made in excel 2007 working in compatibility mode and when I change format in some cell (for example font size), then I save changes and after reopen this fiel the changes was undid. Can some help me? Sorry for my english isn't very well -- thanks Pedro ...

Two Condition Vlookup?
I am currently trying to create a function that searches through an array for two exact column values which allows me to retrieve a third column value. For example: Type of Fruit Date Packaged Amount of Fruit Apples June 100 Oranges June 50 Apples July 75 Grapes July 50 In this example, I would be looking to retrieve the Amount of Fruit (Apples) that was packaged in July. I would be putt...

Change case of text
I have Word 2007. In earlier versions of Word under the Format tab there was a change case option that made it easy to change selected text to all upper, lower, etc. I cannot fund this function in Word 2007. Where is it? -- Thanks for your help - jjk98 Home tab, Font Group, Change Case "Aa" icon. Also, does Shft+F3 toggle the text, from no caps - initial caps - all caps? Hope this helps DeanH "jjk98" wrote: > I have Word 2007. In earlier versions of Word under the Format tab there was > a change case option that made it easy to change selected...

Query condition by date range
I'm trying to create a query to base a report from. I need it to return records within a date range that needs to be specified, as in specifying the beginning date and having the current date as the end date. A field is included in the query that has listed dates in the format mm/dd/yyyy. How would I write the criteria? I have part of it ready - [Please enter starting date:] . I know that's how you get the little question window. On Tue, 4 Dec 2007 14:51:00 -0800, silva wrote: > I'm trying to create a query to base a report from. I need it to return > records within a...

Changing case #4
Is there a way to change case for several cells. In Word, you go to Format, Change case, and then you select all upperlower, lower, Title, etc. Does anyone know if there is a way to do this with Excel 2000? Hi Sandra In Excel you need a macro if you want to change case in the same cell See this webpages http://www.mvps.org/dmcritchie/excel/proper.htm Or http://www.cpearson.com/excel/case.htm Here are three macro's for changing text cells in the selection Sub Uppercase_macro() Dim selectie As Range Dim cel As Range On Error Resume Next Set selectie = Range(ActiveCell.Address &am...

Changing the Administrator password
I know that this has probably been asked many, many times and there is probably a set procedure for it but I still have not been able to locate one. In the state that I work, the education field has taken a pretty big hit as far as layoffs for teachers and support staff are concerned. Hence my upcoming problem! At the end of this month, I will have an urgent need to change the administrator password for our district network since several people from within my department will be leaving the district that know the current password. I know that the administrator password can be ...

Need help with conditional formula (Excel 97)
Can anyone give me the correct syntax for the following formula: if A1 is blank, and B1 is more than zero, then display B1, otherwise display nothing (blank cell) Thank you JD =IF(AND(A1="",B1>0),B1,"") Success! -- met vriendelijke groetjes "Jake D" <JakeD@djhtend.com> schreef in bericht news:crkto4lv30u7uujv27ek8okr0us5t9oj10@4ax.com... > Can anyone give me the correct syntax for the following formula: > > if A1 is blank, and B1 is more than zero, then display B1, otherwise > display nothing (blank cell) > > Thank you > > ...

2007 excel autofilter change back to 2003?
Is it possible to change the autofilter in excel 2007 back to the way excel 2003 worked? I see you have a response to one of your other posts -- in a different forum. jonnybrovo815 wrote: > > Is it possible to change the autofilter in excel 2007 back to the way excel > 2003 worked? -- Dave Peterson ...

OWA changing password problem
When I log onto OWA to change my password, th Internet Service Manager pops up just like it should And all the fields are empty except for the ACCOUN field The problem is that the first letter is missing Exchange2003 on win2003 DC I have the exact same problem. I believe it to be an AD issue since I've seen the same thing with other products. Anyone have any ideas? "gottado" wrote: > When I log onto OWA to change my password, the > Internet Service Manager pops up just like it should. > And all the fields are empty except for the ACCOUNT > field. > The problem ...

Change Password in OWA on Exhange 2003
Were having a problem with the change password in OWA. I have enable the button on the front end server and on the back end server. I have setup the IISADMPWD virtual directory in IIS. When I click the change password button from OWN I get 404 page not found. If I go directly to the page it works just fine. Were running exchange 2003 on windows 2003, I have noticed that the change password files now have a asp extension instead of the normal htr as found on windows 2000 and NT 4.0. Also the options.js file in the exchangeweb/controls directory points to the following..... var szURL ...

Printing list of emails in Oulook Express
I am trying to find a way to print, an entire list of email reciepts, so I don't have to keep them. Can anyone help? >-----Original Message----- >I am trying to find a way to print, an entire list of email reciepts, so I=20 >don't have to keep them. Can anyone help? >. > These Newsgroups address issues regarding MS Office Outlook. Outlook Express is a component of Windows/Internet Explorer. Post your question where the OE gurus hang out: http://communities.microsoft.com/newsgroups/default.asp?icp=3DInternetExp= lorer ...

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Outlook 2003: Attachment Icon doesn't print
All users in a subsidiary office share this attachment icon problem: they see the paper clip in the attachment field prior to opening the e-mail message, and they also see the icon in the opened e-mail (sometimes located below the subject line in the heading, and sometimes located in the body of the e-mail). When they print the message, the icon fails to appear. It will print when you print the message in Plain Text. For HTML it will be fixed in SP1 which is coming soon! -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an O...

<= changes to <?
I have an Access application in which I invoke a report or a form with a where clause. In all cases the where clause is of the form: ServiceDate >= #01/01/2007# And ServiceDate <= #09/25/2007# This runs just fine. I transmitted the MDB file to a client using FTP. when he received it, the <= in all cases had changed to <? in the compiled code ONLY. The source was still correct. I had to go into each code module and force a source change so it would recompile. Any ideas on how or why this happened? -- Bill McCarthy wpmATnorthcst.com I wonder if you may have more luck...

Cannot change to an installed font
I am running Vista Home Premium and Office 2003. I tried to work with a document in which I used a font called Stam College. I tried to add a word in the same line. Instead of the letters appearing in that font, the font automatically changes to Times New Roman and the appearance does likewise. All my other fonts work, just not that one. And it worked fine for a long time, just not today. I tried deleting the font and re-installing it, with the same results. I tried deleting unnecessary fonts from my Fonts folder with no different results. I tried changing printers but no cigar. ...

Conditional Field in Queries
Is there a way to make a field in a query conditional so that it only displays the value if the value is equal to something specifically set? Thanks. Nick Nick.Korynski@gmail.com wrote: > Is there a way to make a field in a query conditional so that it only > displays the value if the value is equal to something specifically > set? Thanks. > > Nick Explain "the value is equal to something specifically set". -- Rick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com ...

change GAL #2
Yes it is. We did a rebuild on the original default OAB and the new OAB. Still, even today after someone does a Send/Receive -> Download Address Book it's still showing people from the other OAB. I also previewed each OAB in ESM to make sure it was accurate, which it is. Any ideas? The one thing I want to avoid is deleting the original Default Address Book and recreating it. "Pierrot Robert" <mcthepro_at_hotmail.com> wrote in message news:uE8YRiJPGHA.3864@TK2MSFTNGP10.phx.gbl... > In the properties page of your mailbox store, is the new OAB listed in the > Gene...