Excluding Rows During Printout

Hi,

Is there a way to automatically exclude rows when printing an Excel
sheet?

I have a list of items (in two colums) in a work sheet similar to
this:

Task           Time
-----------       -------
Task A:       1
Task B:       0
Task C:       2
Task D:       0
Task E:       0
Task F:       0
Task G:       4
Task H:       1

When I print the worksheet I only want to print lines where the time
cell is not zero.  So the printout from the previous data would look
like this:

Task           Time
-----------       -------
Task A:       1
Task C:       2
Task G:       4
Task H:       1

Is there a way to do this?

Thanks for your help,
Kjell
0
3/16/2009 4:36:21 PM
excel 39879 articles. 2 followers. Follow

4 Replies
680 Views

Similar Articles

[PageSpeed] 26

Autofilter for Time <> 0

Print the results.


Gord Dibben  MS Excel MVP

On Mon, 16 Mar 2009 09:36:21 -0700 (PDT), kjell.uddeborg@gmail.com wrote:

>Hi,
>
>Is there a way to automatically exclude rows when printing an Excel
>sheet?
>
>I have a list of items (in two colums) in a work sheet similar to
>this:
>
>Task           Time
>-----------       -------
>Task A:       1
>Task B:       0
>Task C:       2
>Task D:       0
>Task E:       0
>Task F:       0
>Task G:       4
>Task H:       1
>
>When I print the worksheet I only want to print lines where the time
>cell is not zero.  So the printout from the previous data would look
>like this:
>
>Task           Time
>-----------       -------
>Task A:       1
>Task C:       2
>Task G:       4
>Task H:       1
>
>Is there a way to do this?
>
>Thanks for your help,
>Kjell

0
Gord
3/16/2009 5:06:12 PM
On Mar 16, 10:06=A0am, Gord Dibben <gorddibbATshawDOTca> wrote:
> Autofilter for Time <> 0
>
> Print the results.
>
> Gord Dibben =A0MS Excel MVP
>
> On Mon, 16 Mar 2009 09:36:21 -0700 (PDT),kjell.uddeb...@gmail.com wrote:
> >Hi,
>
> >Is there a way to automatically exclude rows when printing an Excel
> >sheet?
>
> >I have a list of items (in two colums) in a work sheet similar to
> >this:
>
> >Task =A0 =A0 =A0 =A0 =A0 Time
> >----------- =A0 =A0 =A0 -------
> >Task A: =A0 =A0 =A0 1
> >Task B: =A0 =A0 =A0 0
> >Task C: =A0 =A0 =A0 2
> >Task D: =A0 =A0 =A0 0
> >Task E: =A0 =A0 =A0 0
> >Task F: =A0 =A0 =A0 0
> >Task G: =A0 =A0 =A0 4
> >Task H: =A0 =A0 =A0 1
>
> >When I print the worksheet I only want to print lines where the time
> >cell is not zero. =A0So the printout from the previous data would look
> >like this:
>
> >Task =A0 =A0 =A0 =A0 =A0 Time
> >----------- =A0 =A0 =A0 -------
> >Task A: =A0 =A0 =A0 1
> >Task C: =A0 =A0 =A0 2
> >Task G: =A0 =A0 =A0 4
> >Task H: =A0 =A0 =A0 1
>
> >Is there a way to do this?
>
> >Thanks for your help,
> >Kjell

Thanks for your answer!  Very cool!  Do you know if it would be
possible to create a filter so that the "output", as described
earlier, would automatically end up on a different worksheet? That way
I would not need to turn the filter off and on as I'm changing the
data.  When I'm done I would simply change to the other worksheet and
everything would already filtered.

Thanks for your help,
Kjell
0
kjell (4)
3/16/2009 10:09:02 PM
You could just copy and paste the visible cells to another sheet.

Or use Advanced Filter to bring the results to another sheet.

See Debra Dalgleish's site for that.

http://www.contextures.on.ca/xladvfilter01.html#ExtractWs

Or you could use a macro to do it all for you.

See Ron de Bruin's site for code to filter a sheet and place results on
another sheet.

http://www.rondebruin.nl/copy5.htm#AutoFilter


Gord

On Mon, 16 Mar 2009 15:09:02 -0700 (PDT), "kjell@cablescan.com"
<kjell@cablescan.com> wrote:

>Thanks for your answer!  Very cool!  Do you know if it would be
>possible to create a filter so that the "output", as described
>earlier, would automatically end up on a different worksheet? That way
>I would not need to turn the filter off and on as I'm changing the
>data.  When I'm done I would simply change to the other worksheet and
>everything would already filtered.
>
>Thanks for your help,
>Kjell

0
Gord
3/16/2009 10:24:43 PM
On Mon, 16 Mar 2009 15:09:02 -0700 (PDT), "kjell@cablescan.com"
<kjell@cablescan.com> wrote:

>On Mar 16, 10:06�am, Gord Dibben <gorddibbATshawDOTca> wrote:
>> Autofilter for Time <> 0
>>
>> Print the results.
>>
>> Gord Dibben �MS Excel MVP
>>
>> On Mon, 16 Mar 2009 09:36:21 -0700 (PDT),kjell.uddeb...@gmail.com wrote:
>> >Hi,
>>
>> >Is there a way to automatically exclude rows when printing an Excel
>> >sheet?
>>
>> >I have a list of items (in two colums) in a work sheet similar to
>> >this:
>>
>> >Task � � � � � Time
>> >----------- � � � -------
>> >Task A: � � � 1
>> >Task B: � � � 0
>> >Task C: � � � 2
>> >Task D: � � � 0
>> >Task E: � � � 0
>> >Task F: � � � 0
>> >Task G: � � � 4
>> >Task H: � � � 1
>>
>> >When I print the worksheet I only want to print lines where the time
>> >cell is not zero. �So the printout from the previous data would look
>> >like this:
>>
>> >Task � � � � � Time
>> >----------- � � � -------
>> >Task A: � � � 1
>> >Task C: � � � 2
>> >Task G: � � � 4
>> >Task H: � � � 1
>>
>> >Is there a way to do this?
>>
>> >Thanks for your help,
>> >Kjell
>
>Thanks for your answer!  Very cool!  Do you know if it would be
>possible to create a filter so that the "output", as described
>earlier, would automatically end up on a different worksheet? That way
>I would not need to turn the filter off and on as I'm changing the
>data.  When I'm done I would simply change to the other worksheet and
>everything would already filtered.

Easily done.  Put in a new column A that increments every time your
time value is more than zero.  It will then look like this:

Increment  Task � � � � � Time
--------- -----------  � -------
1         Task A: � � � 1
1         Task B: � � � 0
2         Task C: � � � 2
2         Task D: � � � 0
2         Task E: � � � 0
2         Task F: � � � 0
3         Task G: � � � 4
4         Task H: � � � 1

The formula in column a, for example in row 3, is:

=if(c3>0,a2+1,a2)

Then copy that down.

On you new sheet have a lookup function that shows the results based
on the row.  Let's say you want to start the results on row 3 (rows 2
and 1 are headers). You would put this in cell A3:

=vlookup(row()-2,'MyOtherSheet'!A3:C10,2,0)  

The result in A3 will be the first row that shows up with a positive
number.

Copy that down as far as you think you need.  You can then get fancy
and encase the whole thing in an ISERR to replace the value with ""
for all rows where the value of row()-2 is greater than the highest
inicrement.

Good luck.
0
dranon (82)
3/17/2009 6:24:27 AM
Reply:

Similar Artilces:

Determining the highest number in a row #2
Hello Excel(lent) users, I have a problem I would like to discuss with you. Kelly, Hank and George are selling different kinds of stuff. Apples, pears, plums etc etc. Each item they sell will be noted and in a pivot table it will be presented. Now I want to know who is the best apple seller, the best pear seller etc etc.. i.e. In a row of numbers I want to determine which is the highest and then highlight it. For instance: Kelly Hank George Total Apples 2 7 ...

toggle total row button greyed out
I've turned on filtering and I need to turn on the total row that sums the filtered data. For some reason the option is greyed out. I've used this before when importing XML data, but never on just a regular spreadsheet and I'm not sure why I can't toggle that option. Thanks for any ideas. I bet you didn't use Data|Filter to see the subtotals before. I bet you used Data|Subtotals. RobR wrote: > > I've turned on filtering and I need to turn on the total > row that sums the filtered data. For some reason the > option is greyed out. I've used this...

Color Scheme Printout
Hi All, back when I was using Publisher98 I printed out a Color Scheme and also a color chart with the base colors down the LHS and numbers across the top. Now that I am using 2007, I can't find this option. Is there one to print out the color schemes and also the color chart? -- Regards, Raymond I think you are mis-remembering. Publisher 98 does not have the print option to print out color schemes, but you could do a print screen (capture). The color chart you are remembering is not in Publisher 2007. There is a similar chart in MS Paint. If you have Vista there is the Snipping ...

Rows are hidden and wont come back
I have this spreadsheet and the row #'s go in order from #1 thru 9131 and then the next row # is 11,725 Question: Why is this happening? Question: HOW did this happen? Question: HOW do I make all of the rows between 9132 & 11725 display? Thanks! Hi The row heigth is set to 0! Select the range 9131:11725, and set the row heigth to >0 -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets "BenJAMMIN" <BenJAMMIN@discussions.microsoft.com> wrote in message news:000F1D28-CDC9-4E45-B420-FD50025B0030@microsoft.com... > I have this spreadsheet...

Count if for every other row
I have this formula that adds every other column in my sheet. =SUM(AH16,AJ16,AL16,AN16,AP16,AR16,AT16,AV16,AV17,AX16,AZ16) I'm trying to countif the columns next to these that are 1 or greater. the ways I've tried the formula return errors. Any insight would be appreciated! Thanks! Try this to add your columns: =SUMPRODUCT((MOD(COLUMN(AH16:AZ16),2)=0)*AH16:AZ16) And try this to count the adjoining columns that are >=1: =SUMPRODUCT((MOD(COLUMN(AI16:BA16),2)=1)*(AI16:BA16>=1)) -- HTH, RD ============================================== Please keep all correspondence wit...

Row highlight??
Is there a way to have Excel highlight a row when you select a cell with the mouse or arrow key? or Is there a way to highlight every other row but not have it print that way? I know how to fill the cell rows with a color but do not wish to have the colors/shades print. I just need a way to highlight every other row or at least the current row where the selected cell is as a visual aid on the display for easier inputting. Reason why I don't color fill every other cell: I will be inserting rows in the future which would mess up the "every other row" method. I am using Mi...

Datedif exclude weekends
Hello newsgroup gurus and users. Your kind assistance please. This question has probably been asked a millions times but I cant find it. Does any one have a solution to this. I want the date difference between two dates but exclude weekends. Many thanks Paul Paul, The NETWORKDAYS function will return the number of days, excluding weekend and optionally holidays, between two dates. See help for NETWORKDAYS for more information. This function is part of the Analysis Tool Pack add-in so you must have that add-in loaded; otherwise, you'll get a #NAME error. -- Cordially, Chip Pe...

Timestamp a cell when row is updated.
I am looking for a solution. My spreadsheet has 5 columns that i do not want this to affect. The 6th column is "Last Updated". When i write comments in any cell after H i want it to put the updated date and time into "Last Updated" in that row. Another addition to this would be to check the last updated date, if it is upto 3days from today then turn green, upto 5days yellow and more than 7days red! I hope someone can help! Hi, generally this is very easy and the code is below but your question isn't clear. Is the 'Last updated' colu...

delete rows that have a duplicate id keeping the upper row
I neeed a macro that will delte a row based on a duplicate value in column A; Row col A col B col C Claim Number DCC Warranty Code 1 0132482B DTYC 03D 2 0137448A 1 3 0141614A 01D 4 0141614A 5 0141614A 6 0143504B 1 7 0154120A DW77 01D 8 0154120A 9 0159953A DWL0 03D...

VB code for checking if a field is not null (for locking a row) is needed
hi, I need to automatically lock a field after it's been written into and has lost focus. Right now I'm using a checkbox, but I want to lock automatically once the field is dirtied (written into) and it loses focus. Actually I need to lock a row, but a field is a good start--I can replicate the code for each field in a row I guess. Any ideas? RL What I'm using now; it works, but you manually have to check a checkbox, and I want to automate this: If boolcheckbox = True Then field001.Locked = True Else field001.Locked = False End If On Dec 24, 8...

Creating a drop down like menu for row of cells?
I have a bit of a problem with an excel worksheet that I have going, th whole sheet is about 450 rows with various bits of input data, an sub-totals for various sections calculated throughout it. In order to reduce the size and make it a little bit easier to navigat through, I am wondering if there is anyway to make rows drop down, lik say with an arrow to the side that a user can click on to have a grou of cells drop down, that way I can only have sub totals and su headings showing. Is this possible to do and how? I've tired searching through help but can't find anything, and don&#...

gci and -include and -exclude
I am trying get a limited set of files from a directory tree. The following is what i want but it doesn't work. I get everything. get-childitem -LiteralPath \rm5_0_code_h\source -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -Recurse I have even tried to exclude what i don't want but it doesn't seem to matter. get-childitem -LiteralPath $path -include *.frm,*.ctl,*.bas,*.cls,*.cs,*.cpp,*.hpp,*.sql,*.java -recurse -exclude *alter*.sql,*.dll,*.pdb,*.ncb,*.tlb,*.scc,*.bmp,*.ico i still get them all. what am i doing wrong? dan you have to a...

allowing rows to break across pages
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel how can i get a row to break across two pages? i can do it in word, and i knew how to do it in excel 04, but i can't figure out how to get it to work in excel 08. what i'm trying to do is the equivalent of the &quot;allow rows to break across pages&quot; function for tables in word (table&amp;gt;table properties&amp;gt;row). there is no similar function in excel. essentially, what i am trying to do is prevent excel from moving a row to a new page once it reaches a certain length. in other wo...

Format options when format is selected from the top row
...

Exclude a cell
Excel 2002 Any one know how I can sum all the cell values in a column except the value of the last cell to be entered which could be say half-way down the column? Is there a function to exclude a particular cell from "sum" ? I know a lot of ppl are far more advanced than I am but we all start somewhere lol Thanks for your help Dale To exclude cell A16: =SUM(A:A)-A16 Not very elegant, but effcient! Cheers, -- AP "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: xB6sg.51172$181.12831@fe3.news.blueyonder.co.uk... > Excel 2002 > &...

How to delete rows automatically with values zero out?
Hello, I have a spreadsheet with hundreds of rows. Many of them were entered at the beginning and then were reversed out. I got this spreadsheet from our ERP system. It is just like this: Part# Quantity Value 013-001 1 $1.00 013-001 -1 $1.00- 013-002 5 $25.00 013-007 3 $120.00 013-007 -3 $120.00- 022-001 12 $17.00 041-009 7 $251.00 041-009 -7 $251.00- 052-061 10 $30.00 How can I delete those rows autometically with the quantity were zero out. I don't want them show up on my spr...

How do you automatically put spaces between rows in a lengthly w.
I have a lengthly work sheet and I want to put spaces between every row--how do I do that? Automatically usually involves VBA coding. Do you really need the inserted rows? Perhaps you could just change the height of the rows to look double-spaced? Here is a macro to do the row inserting. Sub InsertALTrows() 'David McRitchie, misc 2001-06-30 Application.ScreenUpdating = False Application.Calculation = xlCalculationManual 'pre XL97 xlManual Dim i As Integer For i = Selection(Selection.Count).Row To Selection(1).Row + 1 Step -1 Rows(i).EntireRow.Inse...

How to select full row of List Control when focus is on it.
I am using List Control.How do i select the first sel(Full row) default,when the focus is on the List Control.The sel is selected when we click through the mouse. Please help and guide me Regards Atul. "atul" <a_fund@yahoo.com> skrev i en meddelelse news:cc80ab6.0401270518.4f0748bd@posting.google.com... > I am using List Control.How do i select the first sel(Full row) > default,when the focus is on the List Control.The sel is selected when > we click through the mouse. > Please help and guide me > Regards > Atul. Something along the lines of m_ListCtrl.SetE...

Lost Some Row Numbering
Somehow, when I cloned a worksheet to unused worksheet in my workbook, the numbering (in the left-most column) has lost the 1st 5 values (I failed to copy the 1st 5 rows). IOW, my worksheet starts with Row 6 and continues down from there. I can't find anything in Google that tells me how to fix this, so I'm asking here. Please advise. TIA hi, these lines are probably masked -- isabelle Le 2012-01-19 17:22, Mike Copeland a �crit : > Somehow, when I cloned a worksheet to unused worksheet in my > workbook, the numbering (in the left-most column) has lost the ...

Row Height
Hi All, Can anyone help with this. Is there a piece of code that will change the height of the Row/Rows if there is data in columns. For example: If I had data from cells C6 through to C100, only those rows would be 15 instead of the default of 12.75. Because the data is forever changing, I would want the rows to change with it. Sounds rather pointless I know, but can it be done Many thanks in advance Regards John This macro will scan down column C and if the cell is totally empty it will adjust the height to the default height 12.75, else row height will be 15. Sub Ro...

Excluding zeros
I have stock data in excel which I'd like to chart using the high low close option. Problem is that the data contains zeros on some days. Can I get excel to ignore the zeros instead of plotting them. If not what's the best way of converting these zeros to blank cells so that Excel does not chart them. Thank you Hi Ben, Two ways: 1) replace zeros (or missing data) by =NA() 2) Tools/Options/Chart and specify what is to be done with missing values. Bernard "Ben" <anonymous@discussions.microsoft.com> wrote in message news:075901c3bff5$94a57530$a001280a@phx.gbl... &...

seperating rows
i have an excel worksheet that is formatted as such (number) (date) (conpany name) (street address) (city) (state) (agent) (agent's name) (agent's address) (officer) (officer's name) (officer's address) blank line (number) (date) (conpany name) (street address) (city) (state) (agent) (agent's name) (agent's address) (officer) (officer's name) (officer's address) blank line and so on... sometimes there are more than one agent or officer, none of the information can be sorted accurately by column. i want to maintain the data between the blank lines. it must stay...

Can Excel sort rows by color?
I want to sort a spreadsheet by the color of the rows. Here's a website that will help you accomplish this: http://www.cpearson.com/excel/SortByColor.htm tj "Grant" wrote: > I want to sort a spreadsheet by the color of the rows. Grant, have a look here http://www.cpearson.com/excel/SortByColor.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Grant" <Grant@discussions.microsoft.com> wrote in mes...

flag any change made in row
I want to enter today's date in column A anytime that a change is made in the row, e.g. enter today's date in A1 if any cell from B1 to ZZ1 is changed. Are there any fucntions that can be invoked to do this or does it require some VBA code? Suggestions would be most appreciated. John Keith kd0gd@juno.com You could use an event macro. See JE McGimpsey's site: http://www.mcgimpsey.com/excel/timestamp.html John Keith wrote: > > I want to enter today's date in column A anytime that a change is made > in the row, e.g. enter today's date in A1 if any cell from B1...

Row and Column Viewing #2
Gord, Can the same code be used for singlesheets vs the workbook -- RudeRa ----------------------------------------------------------------------- RudeRam's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=881 View this thread: http://www.excelforum.com/showthread.php?threadid=26574 Rude The code I gave works only on one sheet......Sheet1....and does not affect any other sheet in the workbook. Code is placed in the ThisWorkbook module and runs when workbook is opened. Copy the code then right-click on the Excel Icon just left of "File" on main Men...