Pivot Table - How to hide all rows which is 0

Hi guys


I've made a massive pivot table.. however i want all rows which is 0 in
one column to be hidden. Is there a setting for this, or do I have to
make a macro to go and hide all rows which has 0 in a certain column ?


Thansk


-- 
Ctech


------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27745
View this thread: http://www.excelforum.com/showthread.php?threadid=538405

0
5/3/2006 11:48:00 AM
excel 39879 articles. 2 followers. Follow

2 Replies
445 Views

Similar Articles

[PageSpeed] 50

First enter this small macro:

Sub Macro1()
Dim r As Range
For Each r In Selection
   If r.Value = 0 Then
      r.EntireRow.Hidden = True
   End If
Next

Then, in the pivot table, go to the column that may contain zeros and select 
the cells you want to check.

Then run the macro
-- 
Gary''s Student


"Ctech" wrote:

> 
> Hi guys
> 
> 
> I've made a massive pivot table.. however i want all rows which is 0 in
> one column to be hidden. Is there a setting for this, or do I have to
> make a macro to go and hide all rows which has 0 in a certain column ?
> 
> 
> Thansk
> 
> 
> -- 
> Ctech
> 
> 
> ------------------------------------------------------------------------
> Ctech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27745
> View this thread: http://www.excelforum.com/showthread.php?threadid=538405
> 
> 
0
GarysStudent (1572)
5/3/2006 12:38:02 PM
Thanks Gary's Student


-- 
Ctech


------------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27745
View this thread: http://www.excelforum.com/showthread.php?threadid=538405

0
5/3/2006 12:56:30 PM
Reply:

Similar Artilces:

8.0 Word Mail Merge
Has anyone got any experience working with the Word Mail Merge Feature? I am trying to get it working for a division in the UK. When setting the sender phone number, 12345 67890 it automatically formats it as (123)456-7890. It needs to be 12345 67890. I tried to enter a picture mask : {MERGEFIELD LetterAuthorPhone \# ##########} it returns - 456 If I try {MERGEFIELD LetterAuthorPhone \# "##########"} (123) 456-7890 No idea why.... anyone ideas? ...

Pivot Table Fields
I added a couple of columns to an Excel 2007 spreadsheet. When I went to generate a pivot table, all but one of the fields that I added do not show in the field list. I've refreshed the table, got out Excel & tried again all with no luck. Anybody have any suggestions as to why all the fields are not showing? You'll have to change the source data range to include all the new columns. Select a cell in the pivot table On the Ribbon, click the Options tab Click Change Data Source, and click Change Data Source Change the Table/Range reference to include all the new ...

Need to hide the error
Hi ... I guess this is always going to be a problem fo me. So sorry for asking for help like this many times. I have this formula here: =SUMPRODUCT(ROUND(F8*4,0)/4) that keeps giving me this "#VALUR!" error when nothing is being entered, of course this can be very annoying when presented and I want to know if anyone can help me, and actualy explain to me how it was done. Just a little info since I really want to tackle this problem on my own next time. Thanks again in advance. I really appreciate it. Koji Hi first you don't need SUMPRODUCT for this formula. The followin...

How to hide a GUI program with systray icon?
Hi, Everybody! I have a GUI program which can minimize itself to the systray; What I need to do is to hide it's systray icon so that people won't know it is running by looking at my screen. Will it do if I hook Shell_NotifyIcon? Thanks! You can hide the icon by simply removing it. And show it by simply reinserting the icon in the taskbar. AliR. <j012xjj@gmail.com> wrote in message news:1153114375.184762.88760@m73g2000cwd.googlegroups.com... > Hi, Everybody! > > I have a GUI program which can minimize itself to the systray; What I > need to do is to hide it'...

Pivot table data refresh at the pull down
While creating pivot tables, all data under a field will create an option to check mark or include in the current pivot table. If data was created in error it remains as an item to select when you pull down of that field in the final pivot table. How do I refresh this periodcally without rebuilding the entire table? There are instructions here for clearing old items from a PivotTable dropdown list: http://www.contextures.com/xlPivot04.html LKay wrote: > While creating pivot tables, all data under a field will create an option to > check mark or include in the current pivo...

Counting the Same Rows
I have a spreadsheet with 2 columns column a is CustomerID and Column B is TransactionDate I need a way of counting how many transaction dates a customer as column A can have the same customer ID in multiple times Any help appreciated Steve -- Steve M ------------------------------------------------------------------------ Steve M's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32520 View this thread: http://www.excelforum.com/showthread.php?threadid=534495 Is it just =COUNTIF(A:A,"Customer A") or do you need to test the dates as well =SUMPR...

sum value then insert row
How can I make for loop? the conditions are: if total value column height = 5 and column Article = "bike" then copy second row to row 6 (therefore 1 row added between file 5 and 6) thanks for your response ...

SmartList Builder--Row Totals and Grouping?
Hello: As we all know, tools such as SQL Server Reporting Services and Crystal Reports can be used to group say rows of sales amounts for invoices by customer. In that scenario, you can create a total amount of sales per customer. I'm pretty sure that SmartList Builder cannot conduct such group totals. Am I right or am I overlooking something? I know that there is the Summary SmartList feature and I experimented with it. But, that really did not show any sort of "summary totals". It just does things such as show number of customers per city or something like that. S...

Increasing Row Amounts to More than 65,536
Is there any way to get a worksheet with more than 65536 Rows? I need about 75,000 rows. Thanks, Craig Hi! The short, cruel answer is : buy one. Excel won't do it. Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforum.com/showthread.php?threadid=26939 ...

Hide Zero Value Data Points
I am using a bar graph to chart services by the quarter. I need data label values on the data series that has a value of more than zero but would like to hide the data labels for any value that is zero. Is anyone aware of a way to hide the 0 value data label until that value of that series is more than zero? Thanks for any suggestions -- Dewayne Hi, Use a custom number format (whole values only) 0;;; Cheers Andy Dewayne wrote: > I am using a bar graph to chart services by the quarter. I need data label > values on the data series that has a value of more than zero but would ...

add number of duplicate fields in a row or column and have total
I have a log of jobs done on a photocopier, and need to bill people for what they have used. the log is very long and have the user name and the number of colour and number of Black and White copies they have done. I need to know how much colour and how much black and white jobs have been done by each user. hope someone know a way thanks or am I better to use Access ? J =SUMPRODUCT(($A$1:$A$20="Bill")*($C$1:$C$20)*ISNUMBER(FIND({"C","B"},$B$1:$B $20))*{3,2}) THe assumptions made People in A1:A20 Type in B1:B20, C for colour, B for B&W Quantity in...

Filter Juction Table and related tables
Hello I have a Relational DB in Access 2003. I have a Junction Table with (PK OOB and WordID) Keys. The Junction Table is linked many to One to TOOB and TWrd. TOOB [ObrID(PK), Fld1, Fld2, Fld3, Fld4 and Fld5] TWrd[(WrdID(PK), WordName} I need to filter in an unbound txtbox by Wordname entering one, two, three or four words separated by OR or AND operator (Which operator you recommend?). In my continuous form in the Form Header I have an unbound txtFilter to enter the words and a bButton (bFilter) to trigger the search. When records are identified in Junction Table, reco...

EXCEL HELP (ROWS & DATA)
I have a table in Word that has many rows and cells with text in it. I have a column of text in excel. I would like to get in excel the information from the work document of just the text that is found in my excel column. I tried vlookup but the document in word may have a sentence in it and vlook look at a whole cell not part of it. Thanks for the help very much. Example: word docuement has "table four is next to table three" in excel I have a column ...

Transform row to a column
Hello all, Can i make values that listed in a a row, to shown in a column automatically? for a example cells A1:1 B1:2 C1:3 D1:4 to become cells A1: 1 A2: 2 A3: 3 A4: 4. Thank you in advance! -- dealwi8me ------------------------------------------------------------------------ dealwi8me's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18854 View this thread: http://www.excelforum.com/showthread.php?threadid=549315 dealwi8me - One way: Select A2:A4. In the formula bar type "=transpose(b1:d1)" (without the quotes), but don't press Enter....

Add rows to a formula when copying
I have the following formula in cell C3 =IF((Calculations!C9-Calculations!C15)=0," n/a",Calculations!C3/(Calculations!C9-Calculations!C15)) I wish to copy this to cell C16 but wish to add more than the 13 row (ie 16 - 3) to each reference in the worksheet Calculations. I need i to add 27 rows. The end result formula I need in cell C16 is =IF((Calculations!C36-Calculations!C42)=0," n/a",Calculations!C30/(Calculations!C27-Calculations!C42)) Is there any easy way to do this? Happy to do it using a macro i necessary. To sum up, I would like to copy a formula and...

Budget Table
I am creating a database to track what I spend my money on. I am planning to include the following fields in my table. Expense ID Expense Category Purchase date Expense Item Description Projected Cost Actual Cost Amount Saved Comments Considering my intention to track what I spend my money on,what do you think of my table design? Would you recommend different field names, additional field names, or keep the ones I have? Hello Jazz, Consider adding an expense category table. You probably spend your money repeatedly on many of the same things such as electric, gas, ...

How to check Pivot Table source?
Hi, I use a file created in Excel 2003 and need to check if the Pivot table calls proper ranges. Is there any way to check what the source for the Pivot is? And, can the source range be redefined for this Pivot? I don't want to just start new pivot from scratch, as there are different formulas on that spreadsheet that uses its values. Thanks, Marcin Hi Marcin, > I use a file created in Excel 2003 and need to check if the Pivot table > calls proper ranges. Is there any way to check what the source for the > Pivot is? And, can the source range be redefined for this Pivot...

Displaying Text, not numbers, in Pivot Table
Hello all. I posted this in the general section but I think it also could apply to here since I am new with using Pivot Tables. Currently I have a database in excel which has been made into a pivot table. It is just a simple catalog of some books. The row fields are organized as category, field, sub field and then title. The data items are a brief summary, catalog number, and where the book is currently located (in the office or someone else has it). My problem is all of the items in the data field are currently being displayed as numbers, which are all 1s since it gets that specific with t...

How do I set a pivot table to group quarterly reports by F/Y?
I'm trying to set up a report in Excell using the pivot table function. When I use the "Group Field" function to generate a quarterly report it assigns each quarter according to a calendar year which puts the Jan 2010 data in the 1st QTR and the Oct 2009 data in the 4th Quarter. I want to change this so it begins the 1st QTR in Oct. Is there a way to change the way the pivot table reads and organizes the data? Hi Mark You can aggregate whatever months you want to Quarters manually. Don't use Quarters in the Date Grouping, just use Month Highlight the 3 mon...

Smarttags 10.0 with Office 2007
I am trying to install Smarttags and I am having an issue with them being recongnized in any Office Product. I am on GP 10.0 and have installed Smarttags for 10.0. I have given my user security to Smarttags in all of the groups I am a member of. I am using Office 2007. In Excel it does recognize the Smarttags. When I enter a vendor or a customer, it is not recognizing them. The funny is I have only 1 vendor that it does recognize. Has any else have a problems with Smarttags in Office 2007? Does anyone have any suggestions on correcting this? I have went thru all the troubleshoo...

how to fix starting point when counting rows?
is there a way to feed in the the starting cell to begin from when counting the number of rows? i can get the below to work in a macro Dim countRows countRows = Range("C9").End(xlDown).Row but when i try to turn it into a function i cant figure out how to give the starting cell as an argument tks for any help Put "C9" (without the quotes) in cell A1, for example, then you can do this: =3DCOUNT(INDIRECT(A1&":C100")) or =3DCOUNTA(INDIRECT(A1&":C100")) Is that what you mean? Hope this helps. Pete On Aug 29, 4:08=A0pm, polarcap <mem_...@...

Offline with 4.0 and McAfee Antivirus
We're running a pretty standard 4.0 implementaion alongside McAfee VirusScan Enterprise 8.5.0i. When trying to go offline, the synch process breaks and the AV software alerts and deleted a file called {3FD2B963-1730-DD11-98CF-0019B9EB88DE}.bcp it thinks it's a virus. Anybody else seeing the same thing or have any ideas on possible solutions? isn't it possible to exclude the affected folder from scanning? "Matt" <mgd@kynetix.com> schrieb im Newsbeitrag news:%23$a%23$2KxIHA.5124@TK2MSFTNGP04.phx.gbl... > We're running a pretty standard 4.0 implementaio...

Link Tables
I am currently building a Customer database, which comprises of 2 parts, namely the the Customer List detailing customer information and Customer Visit Log detailing dates and times of visits to their site. This will be in two separate forms. Just wondering how I can link up the field in both tables together. Because I wanna show the Company Name and Plant Location in both forms but wants it to be inter-connected. Not really familiar with Access functions and stuff. Thanks in advance In order to do this correctly and avoid future problems you should have a Primary Key Field in each ta...

4.0 enterprise edition license
Hi all, We plan to install ms crm 4.0 enterprise edition for one of our customer. 5 organizatons will be created and 20 users will be users of each organization. My question is: A new license is necessary for each user for every organization? I mean, do I need to purchase 100 licenses (20 users X 5 organizations) or 20 license is enough? Hi, if you use 20 different users, you need 100 licences mit freundlichen Gr��en Michael Sulz ieQ-network AG eMail: michael.sulz@ieq-network.de Thank you for the reply. In the test environment, I have created 5 organizations and 3 users. These 3 us...

SQL Server 2008 requires Microsoft .NET Framework 2.0 SP2 to be installed
http://sanssql.blogspot.com/2010/01/sql-server-2008-requires-microsoft-net.html Regards, Sandesh Segu http://sanssql.blogspot.com Join me @ http://www.orkut.co.in/Main#Community?cmm=96088888 ...