Solved

Selecting all the check boxes excluding #N/A and Blank under Row labels in a pivot table

Posted on 2014-12-22
5
512 Views
Last Modified: 2015-01-06
Hi,

My source data has empty cells and #N/A.  When I Pivot Table this data - the Pivot Table shows "(blank)" for the empty cells and #N/A under Row labels. I would not want to manually filter and uncheck (blank) and #N/A every time.

Let me know if anyone have a VBA code to perform this. And also it would of great help if you have the code to check all the boxes or check only few boxes as desired under row labels in the pivot table.

So ultimately i need a VBA code that will:
1) Automatically hide any row that contains the words "(blank)"/ "N/A" under Row labels.
2) Automatically Check all the boxes under Row labels.
3) Automatically Check only few boxes under Row labels.

Thanks,
Ganesh
0
Comment
Question by:Ganesh Vijaykumar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40516073
Hi,

Could you send a dummy example?

With ActiveSheet.PivotTables("PivotTable1").PivotFields("MyPivotField")
        .PivotItems("(blank)").Visible = False
        .PivotItems("#N/A").Visible = False
End With


Regards
0
 

Author Comment

by:Ganesh Vijaykumar
ID: 40521721
Hi Rgonzo1971,

Thanks for your response!

Hope you had a great Christmas! Could not respond you immediately as i was on holiday.

The above code worked for me thanks a lot again. But it only for worked to deselect #N/A / Blank under the pivot table.  

As i mentioned in the above question (Point number 2) i need code for
2) Automatically Check all the boxes under Row labels.

I am working on a VB scripted template where the data changes every week. The problem i am facing is every time data changes only few items will checked/selected under row labels in the pivot table. I have to manually check all the boxes every time. I have attached the word document for your reference.

It would of great help if you could help me with the code which automatically selects all the check boxes.

Will be awaiting your positive response!

Thanks,
G
0
 

Author Comment

by:Ganesh Vijaykumar
ID: 40521723
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40521765
then try

For each pvtItm in ActiveSheet.PivotTables("PivotTable1").PivotFields("MyPivotField").PivotItems
        pvtItm.Visible = True
Next
0
 

Author Comment

by:Ganesh Vijaykumar
ID: 40533356
Hi Rgonzo1971,

I used the above code but still unsuccessful in automatically selecting all the check boxes. I am relatively new to VBA coding, may be I am not placing the code at the right place.

It would of great help if you could help me editing the below mentioned code, in such a way that script will automatically selects all the check boxes under row labels in pivot table.


Below is the code I am using to fetch the data from one excel sheet to another and refreshing pivot table.

Sub PVDetails_DataFetch()

Dim filespec As Variant
Dim filename As String

    MsgBox "Please select the pvdetails Week File"

    filespec = Application.GetOpenFilename("Excel-files,*.xlsx", _
        1, "Select the PV Details File ", , False)
         If TypeName(filespec) = "Boolean" Then Exit Sub
    Workbooks.Open (filespec)
    Sheets("PV DETAILS LAST WK-S").Select

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
   
       
    Windows("PV Template Automated Template.xlsm").Activate
    Sheets("PV DETAILS LAST WK-S-RD").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

   
    Workbooks.Open (filespec)
    Sheets("PV DETAILS THIS WK-S").Select

    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

       
    Windows("PV Template Automated Template.xlsm").Activate
    Sheets("PV DETAILS THIS WK-S -RD").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
    Range("B5").Select
    ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
   
    Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
    Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("PV DETAILS THIS WK-S").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("PV DETAILS THIS WK-S -RD-PIV").Select
    Range("C6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("PV DETAILS THIS WK-S").Select
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("PV DETAILS LAST WK-S-PIV").Select
    Range("B5").Select
    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
   
    Sheets("PV DETAILS LAST WK-S-PIV").Select
    Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("PV DETAILS LAST WK-S").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    Sheets("PV DETAILS LAST WK-S-PIV").Select
    Range("C6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("PV DETAILS LAST WK-S").Select
    Range("M2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Sheets("Macro").Select
   
   
End Sub


Please do let me know if you need any additional information!

Thanks,
G
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Counting Timer - Up and Down 26 63
Modification to nested formula needed 2 25
any combination of this numbers 9 33
Overwriting Named Ranges 13 31
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question