Hiding blank rows below Pivot Table

[K. Georgiadis]

I have a Pivot table with 66 rows data in 9 columns. I only want Column Grand Totals for the last 3 columns (H, I, and J) and, since selective grand totals are not possible, I deselected the automatic column grand total option. Instead on row 72 I put the simple formula =sum(H6:H71), etc. which does the job.

The problem arises that the maximum of 66 rows are used only when the user selects "All" customers from the Page drop-down list. Depending on the customer chosen, the PT may have as few as 5 rows, whereas the Grand total always appears on Row 72. Is there a way to hide the blank rows betwen Row 72, where tha grand total is shown, and the last populated row of the pivot table?

[YellowLabPro]

Can you put up a sample worksheet?

[K. Georgiadis]

I am not sure how for a number of reasons: (a) the data is confidential to a third party who forbid me to share (b) the Excel file itself is 2.9 megs and (c) is linked to an Access file of 40 megs.

[YellowLabPro]

Can you make an example of what you are doing by copying the workbook and changing the source data so that it is just random info and we can see what is happening?
This will reduce the size and protect the anonymity of your client.
Are you facing this difficulty in VBA or just running the PT from w/in Excel?

[K. Georgiadis]

It's not really a difficulty, it is a little awkward to always have the grand total on row 72 if (in some cases) the Pivot Table ends on Row 7. I have been running it in Excel but I felt that there must be a way to hide with VBA code the unused rows between row 72 and the last row populated by the PT.

[YellowLabPro]

Where does your range begin, column?
Where do you want to target as the empty row index?

Ps. I am not getting emailed when there is a new reply here, so I apologize for not checking back sooner.

[Brandtrock]

Check out the conditional formatting in the pivot table in the attachment. Perhaps this would work for you.

Regards,

[YellowLabPro]

Brandtrock,
where exactly do you have conditional formatting?

[K. Georgiadis]

The PT table starts on Row 1; when the user selects "All" (distributors) the PT, at its largest, occupies range A1:J71. The grand totals are always on Row 72. Depending on which distributor the user selects from the "Page" drop-down list, the PT may have as few as 5 rows. The objective is to hide all Rows between Row 72 and the last row occupied by the Pivot Table.

Thanks for your help. Don't worry about not getting email notifications of my posts; I know that can happen to forums like this from time to time.

[K. Georgiadis]

Check out the conditional formatting in the pivot table in the attachment. Perhaps this would work for you.

Regards,

I'm not even familiar with this PT arrangement with the totals to the side!:(

[YellowLabPro]

All I think BrandtRock did was put the PT on the same worksheet as the source data.

I will work on this, but where is your first column of data, A, B, C, and is the first column manufacturers?
This is what I was referring to earlier for the index column, the column that you will hide blank rows.

[YellowLabPro]

Option Explicit
Sub HideRows()
Dim x As Long
Application.ScreenUpdating = False
For x = Range("TotalRow").Row - 1 To 8 Step -1
Range("A" & x).EntireRow.Hidden = Len(Range("A" & x)) = 0
Next x
Application.ScreenUpdating = True
End Sub

[Brandtrock]

YLP, The conditional formatting I mentioned begins in the second row of the pivot table under each of the products. The screen shot attached is for cell H4 of the example. The left border needs to stay "visible" in this column. The "middle" (column I - Product B) doesn't need this, (column J - Product C) is next to (column K - Product D) which is the only one in the example to "show" totals so it should leave the right border "visible".

The formatting is different depending on the column, but the condition is the same for each.

KG,
Regarding the layout, if your totals are on the left, you can click in the column, and move above the data area to get them as I have laid them out. This is handy to do sometimes, other times not, just depends on your data and desired final result.

As far as including it on the same sheet as the data goes, that was simply to keep the file smaller.

Regards,

[YellowLabPro]

Thanks Brandtrock,
I could not find that guy to save my life....
So by searching on the 5 characters from the appropriate cell in "G" how does this filter out the blank rows?
I removed March completely from the source data, and still have one blank row, (blank).

thanks for having a look

YLP

[Brandtrock]

It doesn't filter out the blank lines. KG said he eliminated the totals from his PT and then put his formula for totals in row 72. That meant he needed to hide rows. Using the Conditional Format solution, he would turn the totals back on, then using the CF's in the sixth through the seventy first row, it would blank out the unwanted cells in the PT totals regardless of which row they fall in.

Regards,

[K. Georgiadis]

I copied this code onto the worksheet with the PT but nothing happened when I selected a small customer, which generated some 70 blank rows.

[K. Georgiadis]

The names of the products appear on column A. The name of the customer is selected from the drop down box in Cell 1B (the "page," or "filter" area). I would give you a screen shot but I did not manage to save the screen shot in JPEG format, as Brandtrock has done!

[YellowLabPro]

K-
When you say you copied the code onto the sheet, you mean you placed the code into a module and ran the code, or you were following Brandtrock's CF solution?

[K. Georgiadis]

The user(s) of the Pivot Table are unfamiliar with VBA and macros. What I was hoping to achieve is that each time they select a customer that created blank rows, the blank rows would hide themselves automatically.

[YellowLabPro]

K-
I am sure this is possible, but I either will need to see some sort of data that shows me how you want it or I will have to pass, I am new at programming and just able to work through things at this point. There are other people here that are much more qualified.
Also, adding a button to the worksheet should handle your request I believe, but that is why I was asking if you added the code to your workbook or not. We can add a button to the code I submitted you. If it does not run then most likely the target columns are off and need to be adjusted, so either you will need to edit, or let me see something that will help me identify where the target columns are.

[Brandtrock]

YLP,

I'm not sure you'd need to use a button, the Worksheet SelectionChange Event fires when a PT page field is changed. The code could go in there and should work as KG desires.

Regards,

[YellowLabPro]

Hi B,
Yes, good point and thanks for the confirmation. I am still very new w/ VBA... and I have not tackled Worksheet Events independently, though I know they are there and been provided some and have used those.

I was going after the simple button approach first, then after getting that to work, we could get some help from the board for the Change event trigger as you suggest as the better solution.

I just cannot get from KG whether my code failed or whether it has not been place in the proper location, eg. in a module....
And, sense you offer a different approach w/ CF as a solution and I thought I might be misunderstanding the OP's intent based on your previous reply. So I am trying to get KG to provide a visual....

KG,
I recognize that you cannot put up the real data, but if you offer some psuedo data in the format, I am better prepared to help to solve... :-)

[K. Georgiadis]

If I couldd figure out how to make screenshots that I can attach to my posts, I would have sent you a few examples. There is another complication: the PT tracks sales by product by customer for 3 years, 2003, 2004, 2005. The column grand total (a simple Excel =SUM formula) is always on Row 72 but the column changes depending on how many years sales history the selected customer has. If the client has a 3-year sales history, the totals appear on H72, I72 and J72. If the client only has a 2-year sales history, the totals appear on F72 and G72. If the customer only has 1-year sales history, the grand total appears on D72.

As you can see, it's not only the number of rows but also the number of columns that vary depending on the customer selection made with the dropdown list. Which may be why your formula did not work (I copied it on the worksheet and tried to run it manually from the Macro menu).

At this point, I am inclined to let the PT insert the column Grand Totals automatically and live with the fact that it will also aggregate product volumes and unit prices -- both of which are completely meaningless numbers.

Thank you very much for the effort and the willingness to help. :beerchug:

[YellowLabPro]

Sure thing,
If you change your mind.... offer stands. If you like, you can change the customer's names and any other private info and send me your sheet. Just PM me for further details.

YLP

[Brandtrock]

KG,

Hit the Print Screen button on your keyboard. This places the screenshot on the clipboard. Launch Paint. Paste into Paint. Use the rectangle tool to select the area you wish to attach to your post. Start a new Paint file and paste the selected area there. Answer No when prompted to save changes to your first file.

Save the file with the selected portion as a jpg file. Now you can attach it to a post.

If anyone else has a quicker way, please let me know as this method is cumbersome, but it gets the job done.

Regards,

[YellowLabPro]

B-
Yep, I just figured out how to do it in less steps....
After pasting to Paint, there is a menu button, three shapes, use the top one. This crops your image to the size you want, crop it, copy it, Ctl+N, do not save and paste to the new canvas....

Even faster way, select the area on your excel file and Ctl+C to copy, then paste that directly into the paint file. It handles everything.... and then save and then post....

[tpoynton]

There are a few free programs that will do screenshots of only selected areas of the screen. my favorite is from wisdom-soft (http://www.wisdom-soft.com/products/screenhunter.htm), and the free version does everything I need it to! copies as a jpg to clipboard, but you can configure it to save to a jpg file too.

[Brandtrock]

The shortcuts are much appreciated as is the link. I'll give the free thing a whirl (yeah, I'm ultra cheap) to see how I like it.

Regards,