Hiding rows in a pivot table

[lgbatdw]

I have a pivot table with 16 rows at the bottom containing financial info that the user can Hide or Unhide (using buttons). The problem is, when these rows are hidden, as the user pulls page flds down into the column area, it bumps down the bottom rows that should always be visible into the hidden area - resulting in rows that need to be shown but are not. This is my code behind the "Hide Financials" "Unhide Financials" buttons. How can I fix this so that as the page flds are pulled down, it somehow adjusts and shows all the rows it's supposed to. Hopefully I've explained this clearly enough and someone has a simple fix. I am not well versed in Excel. Thanks in advance for any help.

Sub UnhideFinancial_Click()
Rows("53:69").Select
Selection.EntireRow.Hidden = False
Range("D3").Select
End Sub

Sub HideFinancial_Click()
Rows("53:69").Select
Selection.EntireRow.Hidden = True
Range("D3").Select
End Sub

Linda in MN

 

[StuckInTheMiddle]

What you have done is a 'hard coded' solution which as you have noted works when you know your data in the pivot table is going to be a set number of rows long.

What you need to find is a method to programmatically determine how many rows of data your pivot table has and hide the last 16 rows no matter what the length. You may have better luck posting this question in the VBA forum is a macro is going to be required. Sorry I'm not fully versed on limiting Pivot Table data.

A,

 

[barbola]

Is it always the last 16 rows (ie Totals), or always the same data for the pivot table? You can 'uncheck' the data so it doesn't get included in the pivot table, for example if your data is showing a list of customers, you can 'hide' certain customers by unchecking them from the dropdown list in the Customer heading.

 

[lgbatdw]

barbola:

It is always the bottom 16 ROWS. These rows contain financial stats that they don't always want to show on the printout to distribute, but may want to review it or print for key authorized personnel. So I do need to be able to show or not show the entire row of data (16 rows).

I agree with "StuckInTheMiddle" who replied to this thread noting that I had "hard coded" the "hidden" range. Is there a simple way to achieve this without hard coding?

Linda in MN

 

[xlbo]

yes but it involves the use of VBA in a macro

If you know any VBA, have a look in Forum707 - there are at least 2 FAQs for finding out the last row

Once you have found the last row, you may just subtract 16 from it so:

Sub UnhideFinancial_Click()
Rows(LastRow-16 & ":" & LastRow).EntireRow.Hidden = False
Range("D3").Select
End Sub

Sub HideFinancial_Click()
Rows(LastRow-16 & ":" & LastRow).EntireRow.Hidden = True
Range("D3").Select
End Sub

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Instead of hiding rows, why don't you hide the relevant PivotItems. That way their position in the sheet doesn't matter.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[lgbatdw]

Glenn:
How do I do that(hide the relevant PivotItems)? What would the macro code be? I must apologize with my lack of Excel knowledge.

Below is a partial sampling of the rows (more columns per row in actual) that would be hidden.

Other Charges $35.18 $0.00
Total Revenue $833.74 $4,500.60

Total Employee Cost $561.95 $2,602.73
Mileage Cost $78.44 $37.00
Gross Profit $271.78 $1,897.87
Gross Margin 32.60% 42.17%


Linda in MN

 

[GlennUK]

The best way to make sure all the spellings are right is to switch on the macro recorder, and then do it by hand. If you choose to view field settings you should see the option to hide items, or untick items ( depending on the version of Excel you are using ). Have a look at the code the recorder creates and see if it helps you.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

is there any actual issue with the code I posted earlier?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lgbatdw]

Geoff:

The code you posted earlier probably works like a charm. However I am inexperienced in Excel and don't have a clue what the code would be to determine the last row in my pivot table. OR, it just dawned on me, is LastRow a property that can be referenced just as your code shows?

Linda in MN

 

[KenWright]

Silly question perhaps, but why not just add a field to the source data, and then use a VLOOKUP or something perhaps to determine whether that field should be "SHOWN" or "HIDDEN" and then throw that into the page fields. You can then choose with a single click whether or not to display them.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

as per my post. In the VBA forum (Forum707)

there are at least 2 FAQs for finding out the last row

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question