using VBA to hide rows in a pivot table

[gayjaybird]

Sub Hidesingles()
  For i = 5 To ActiveSheet.UsedRange.Rows.Count
      For c = 3 To ActiveSheet.UsedRange.Column.Count
        If Cells(i, c) <= 1 Then Cells(i, 1).EntireRow.Hidden = True Else: Next c
        End If
Next i
End Sub

I'm trying to filter a pivot table with VBA. The spreadsheet/database I'm summarizing has fields for the store name, the package tracking number, the date and the sender's store name. Row headings are store name and tracking number, and the columns are the date. The pivot table counts how many times a particular sender's store name shows up on a particular date. I have the rows collapsed so that the table only displays the subtotals for each store for each day. How can I filter the table so that only the stores who sent more than one package a day are listed? I tried the code above and ended up with a 'Next without For error.' Any ideas?

 

[xenou]

Sub Hidesingles()
For i = 5 To ActiveSheet.UsedRange.Rows.Count
    For c = 3 To ActiveSheet.UsedRange.Column.Count
        If Cells(i, c)<= 1 Then
            Cells(i, 1).EntireRow.Hidden = True
        End If
    Next c
Next i
End Sub

You'd be better off not putting dates in columns...256 columns (Excel 2003) is only enough for a year or less of data. Even if you have more columns (Excel 2007) the design makes it awkward.

This would make a good dataset that a pivot table could summarize in a jif:

book2
	A	B	C	D	E
1	Store	TrkID	Date	Sender	ShipCount
2	Store1	XYD113	22-Sep	Sender1	3
3	Store1	XYD114	22-Sep	Sender2	3
4	Store1	XYD115	22-Sep	Sender3	3
5	Store2	XYD116	23-Sep	Sender4	1
6	Store3	XYD117	23-Sep	Sender5	2
7	Store3	XYD118	23-Sep	Sender6	2
8	Store3	XYD119	24-Sep	Sender7	1
9	Store4	XYD120	24-Sep	Sender8	2
10	Store4	XYD121	24-Sep	Sender9	2
11	Store5	XYD122	24-Sep	Sender10	1
Sheet1

The last formula is an array formula, therefore entered with Control + Shift + Enter instead of just Enter.

HTH[/code]

 

[xenou]

I forgot to mention, with the dataset as above you can just add a page field to your pivot table report -- therefore easily hide stores with only one package on the day. You could also group by stores, by date ranges, by package counts, and of course get your totals...by any of the above groups or by sum totals.

Regards.

 

[gayjaybird]

Thanks, that's a start. Actually, the date issue isn't such a big deal, because this spreadsheet only covers one billing period-- 12 to 14 days, if I recall. They query our business system and it spits out a report in excel format. They want to condense this report so they can bill the stores who send more than one shipment per day.

Here's a screenshot of my dummy pivot table

I want to hide the information for Stores A and D, and display the information for the companies with the highlights. This is just a dummied version for demonstration. The actual Pivot table can expand and collapse the details for each store so users can see subtotals or view the individual tracking numbers.

Does this make any more sense?

Thanks.

 

[xenou]

Along with your pivot, please post the source data that this pivot table is based on, if possible...

Some thoughts off the top of my head:
I am not sure there is any way to dynamically hide the stores you don't want visible. You can hide row fields...but you would have to select the rows to hide and I think you want it done more automatically. Same (even more so) for data fields...you could hide the columns you don't want visible using pivot table options, but it needs to be "smarter" for you purposes.

You could add a column to your data source where you can use a formula to "flag" (true/false or one/zero) stores that have more than one shipment on a day...This could be your page field to show the stores. But with the columns...I am not so sure how you'd filter out the dates you don't want...Stumped. Plus, you have the problem that the same data might be a 1 shipment for one store and a 2 shipments for another store, so in fact you'd need to "show" that column for the second store, even if you wanted to "hide" it for the first...so I am thinking you'd need to show all dates, and then somehow sum the extra shipping. This would be easy "outside" the pivot table, but I'll have to scratch my head some more to think if we can do it "inside" the pivot table.

Anyway, if you'd like to post a set of data that this pivot table is built on (enough to show the relevant fields and the types of data they contain) we can probably work at that level to craft the proper dataset and/or build the results table we need.

As usual in cases such as this, a query-based solution with Microsoft Access or MS Query would demonstrate the power of a simple SQL statement! But we can work a solution with Excel--it just takes a little more work.

Regards.

 

[xenou]

Here's one rather pathetic try I just added a column of 1's to the data table...so you can do addition and subtraction on the number of packages. In this case, the formula only works when the dates are on the table, so if you are expanding the detail and collapsing it this wouldn't work. I am back to thinking lets look at the source data and maybe work there...I usually use pivot tables for very specific purposes, so for instance if I was using a pivot table in a case like this I'd set it up to give me the info I need and then leave it alone...if you need a table with drill downs for other users etc., just build another pivot table report for that functionality. I hope this isn't bad advice! The advantage to having one table for your package counts is that you can then put some formulas "outside" the table and the structure remains stable so you can use these formulas consistently.

Pivot.xls
	A	B	C	D
3			Data
4	Store	Date	CountofTrackID	ExtraShipments
5	StoreA	2-Sep	2	1
6	StoreB	3-Sep	1	0
7		6-Sep	1	0
8		8-Sep	1	0
9	StoreC	4-Sep	1	0
10		7-Sep	1	0
11		9-Sep	3	2
Sheet4

 

[gayjaybird]

I'll get the base data for you on Monday when I'm back at work. As far as filtering dates, I'm not so concerned about it, exactly for the reason you mentioned--store A might send one package on 2-Sep, but store B sends 2.

Thanks for your help!

 

[gayjaybird]

sample data

multiple orders dummy.xls
	A	B	C	D
1	TrackingNumber	Reference1	PickupDate	SenderCompanyName
2	ab12374	StoreA#154	8302007	StoreA#154
3	ab12375	StoreA#155	8302007	StoreA#155
4	ab12376	StoreA#160	8302007	StoreA#160
5	ab12377	StoreA#183	8302007	StoreA#183
6	ab12378	StoreA#185	8302007	StoreA#185
7	ab12379	StoreA#190	8302007	StoreA#190
8	ab12380	StoreA#191	8302007	StoreA#191
9	ab12358	StoreA#2153	8292007	StoreA#2153
10	ab12347	StoreA#2325	8222007	StoreA#2325
11	ab12345	StoreA#270	8202007	StoreA#270
12	ab12359	StoreA#43	8292007	StoreA#43
13	ab12348	StoreA#44	8222007	StoreA#44
14	ab12360	StoreA#479	8292007	StoreA#479
15	ab12352	StoreA#658	8232007	StoreA#658
16	ab12361	StoreA#858	8292007	StoreA#858
17	ab12353	StoreB#1016	8282007	StoreB#1016
18	ab12354	StoreB#1016	8282007	StoreB#1016
19	ab12355	StoreB#1267	8282007	StoreB#1267
20	ab12356	StoreB#317	8282007	StoreB#317
21	ab12346	StoreB#322	8202007	StoreB#322
22	ab12349	StoreB#51	8222007	StoreB#51
23	ab12350	StoreB#575	8222007	StoreB#575
24	ab12351	StoreB#723	8222007	StoreB#723
dummy data

Here's some sample data from the actual report, with confidential information changed. In this sample, only the count for Store B #1016 should show up when I run the macro.

 

[xenou]

It appears to me that you would benefit from a column in your source data to "count" - the problem with pivots is that you can't filter on the data field, so the count has to be rigged up otherwise.

Here's one way:

20071001_packages.xls
	A	B	C	D	E
1	TrackingNumber	Reference1	PickupDate	SenderCompanyName	Count
2	ab12374	StoreA#154	8302007	StoreA#154	1
3	ab12375	StoreA#155	8302007	StoreA#155	1
4	ab12352	StoreA#658	8232007	StoreA#658	1
5	ab12361	StoreA#858	8292007	StoreA#858	1
6	ab12353	StoreB#1016	8282007	StoreB#1016	2
7	ab12354	StoreB#1016	8282007	StoreB#1016	2
8	ab12355	StoreB#1267	8282007	StoreB#1267	1
9	ab12356	StoreB#317	8282007	StoreB#317	1
10	ab12346	StoreB#322	8202007	StoreB#322	1
Sheet1

Formula in Cell E2 is
=SUM(($C$2:$C$10000=C2)*($D$2:$D$10000=D2)*1)
Entered as an array formula with Control + Shift + Enter, not just Enter, and assuming 10,000 rows is plenty to cover all your data that will ever be there.

see next post for pivot...

 

[xenou]

Now, pivot table is easy:
You add a page field at the top, right click on it, select field settings..., and Hide the 1's and Blanks.
For the row fields I've removed subtotals. I also removed Grand Total for Columns from the table options.

20071001_packages.xls
	A	B	C	D	E
1	Count	(MultipleItems)
2
3	CountofSenderCompanyName
4	Reference1	TrackingNumber	PickupDate	Total
5	StoreB#1016	ab12353	8282007	1
6		ab12354	8282007	1
7
Sheet4