Solved

How to exclude "Blanks" from Excel Pivot Chart with VBA Code

Posted on 2014-03-26
8
4,916 Views
Last Modified: 2014-05-10
I've created an Excel Template that contains a Macro that creates a few Pivot Charts.  When I run the Macro, it creates the Pivot Charts, but in the chart there are rows that contain the word "Blank".

On line 62 of the Code, I have tried ".PivotItems("(blank)").Visible = False", but the rows with the word "Blank" are still there.

How can I eliminate the "Blank" words from the Pivot Chart.

I have included the code for the Macro.

Sub macQtrlyAssocReport(Optional Str As String = "")
'macQtrlyAssocReport Macro
Dim ws As Worksheet
Dim i As Long
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
Application.EnableEvents = False

' turn off compatibility checker
ActiveWorkbook.CheckCompatibility = False

On Error Resume Next
    Set ws = Worksheets("Q1 Audit Criteria_Graph")
    On Error GoTo 0
    If Not ws Is Nothing Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If

' Create Monthly Summary
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Detail!R1C1:R35C8", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="", TableName:="Summary", DefaultVersion _
        :=xlPivotTableVersion14
    ActiveSheet.Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("Summary")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("Summary").PivotFields("Manager_Name")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Summary").PivotFields("Assoc Ops Area")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Summary").PivotFields("Assoc")
        .Orientation = xlPageField
        .Position = 1
        .Name = "Assoc Error"
    End With
'    Range("H2").Select
    With ActiveSheet.PivotTables("Summary").PivotFields("Month")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Summary").PivotFields("Quality_Review_Criteria")
        .Orientation = xlRowField
        .Position = 1
        .Name = "Audit Criteria Associate"
    End With

    With ActiveSheet.PivotTables("Summary").PivotFields("Audit Criteria Associate")
        .LayoutForm = xlOutline
        .LayoutCompactRow = True
    End With

    With ActiveSheet.PivotTables("Summary").PivotFields("Employee")
        .PivotItems("(blank)").Visible = False
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Summary").PivotFields("Employee").Orientation = _
        xlHidden
    With ActiveSheet.PivotTables("Summary").PivotFields("Employee")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("Summary").AddDataField ActiveSheet.PivotTables( _
        "Summary").PivotFields("InquiryNum"), "", xlCount

    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.PivotTables("Summary").PivotFields("Assoc Error"). _
    EnableMultiplePageItems = True
    ActiveSheet.PivotTables("Summary").PivotFields("Assoc Error").CurrentPage = _
        "Y"
    ActiveSheet.PivotTables("Summary").PivotFields("Month").CurrentPage = _
        "(All)"
    ActiveSheet.PivotTables("Summary").PivotFields("Month"). _
        EnableMultiplePageItems = True

    ActiveSheet.PivotTables("Summary").ShowDrillIndicators = False

    Range("A7").Select
    Selection.Value = "Audit Criteria Associate"

    Range("A1:A4").Select
    Selection.Font.Bold = True

    Range("B1:B4").Select
    Selection.Font.Italic = True

    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    ActiveSheet.Range("A6").Select
    ActiveSheet.Name = "Summary"

' Create Quarterly Summary By Associate
    Sheets.Add
    ActiveSheet.Select
    Cells(3, 1).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Detail!R1C1:R278C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="Q1 Summary By Assoc", DefaultVersion _
        :=xlPivotTableVersion12
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("Q1 Summary By Assoc")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("Manager_Name")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("Assoc")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("Employee")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields( _
        "Quality_Review_Criteria")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("Employee")
        .LayoutForm = xlOutline
        .LayoutCompactRow = True
    End With

    Range("A5").Select
    With Selection
      .Value = "Audit Criteria Associate"
    End With

    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("InquiryNum")
        .Orientation = xlRowField
        .Position = 3
    End With

    ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("InquiryNum").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("Q1 Summary By Assoc").AddDataField ActiveSheet.PivotTables( _
        "Q1 Summary By Assoc").PivotFields("InquiryNum"), "", xlCount
            
    With ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotFields("Quality_Review_Date")
        .PivotItems("(blank)").Visible = False
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("B5").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, False)
    Cells.Select
    Cells.EntireColumn.AutoFit
    Selection.ColumnWidth = 55.71
    Range("A6").Select
    ActiveSheet.PivotTables("Q1 Summary By Assoc").ShowDrillIndicators = False
    ActiveSheet.PivotTables("Q1 Summary By Assoc").PivotSelect "Employee[All]", xlLabelOnly + xlFirstRow, True
    
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("B4").Select
    ActiveSheet.PivotTables("Q1 Summary By Assoc").CompactLayoutColumnHeader = ""
    Range("B6").Select
    Columns("B:B").EntireColumn.AutoFit

    Range("A6").Select
'    Dim ws As Worksheet
'    Dim cel As Range, rg As Range
'    Dim i As Long
    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count     'This statement is sometimes needed to reset the UsedRange property
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)       'First row and column that contain data
    Set cel = rg.Cells(1, rg.Columns.Count)        'First row, last column that contains data
    Set cel = rg.Cells(rg.Rows.Count, 1)             'First column, last row that contains data
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)        'Last row, last column that contains data

' Put Borders around the Range selection from above
    With rg.Borders
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

' Format certain Columns
    Range("A1:A2").Select
    Selection.Font.Bold = True

    Columns("A:A").Select
    Selection.ColumnWidth = 60

    Range("A1:A2").Select
    Selection.Font.Bold = True

    Range("B1:B2").Select
    Selection.Font.Italic = True

' Move the Sheet to put in a particular order
    ActiveSheet.Move Sheets(Sheets("Summary").Index + 1)

' Wrap Text in Column A
    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("A6").Select

' Rename the Active Sheet
    ActiveSheet.Name = "Q1 Summary by Associate"


' Create Quarterly Summary By Audit Criteria
    Sheets.Add
    ActiveSheet.Select
    Cells(3, 1).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Detail!R1C1:R278C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="Q1 Summary By Audit Criteria", DefaultVersion _
        :=xlPivotTableVersion12
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("Manager_Name")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("Assoc")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields( _
        "Quality_Review_Criteria")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("Employee")
        .Orientation = xlRowField
        .Position = 2
    End With
    Range("A6").Select
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("Quality_Review_Criteria")
        .LayoutForm = xlOutline
        .LayoutCompactRow = True
    End With

    Range("A5").Select
    With Selection
      .Value = "Audit Criteria Associate"
    End With

    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("InquiryNum")
        .Orientation = xlRowField
        .Position = 3
    End With

    ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("InquiryNum").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").AddDataField ActiveSheet.PivotTables( _
        "Q1 Summary By Audit Criteria").PivotFields("InquiryNum"), "", xlCount
    With ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotFields("Quality_Review_Date")
        .PivotItems("(blank)").Visible = False
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("B5").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, True, False, False)
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("A2").Select
    Columns("A:A").ColumnWidth = 52.14
    Range("A6").Select
    ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").ShowDrillIndicators = False
    ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").PivotSelect "Employee[All]", xlLabelOnly + xlFirstRow, True
    ActiveWorkbook.ShowPivotTableFieldList = False
    Range("B4").Select
    ActiveSheet.PivotTables("Q1 Summary By Audit Criteria").CompactLayoutColumnHeader = ""
    Range("B6").Select
    Columns("B:B").EntireColumn.AutoFit

    Range("A6").Select
    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count     'This statement is sometimes needed to reset the UsedRange property
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)       'First row and column that contain data
    Set cel = rg.Cells(1, rg.Columns.Count)        'First row, last column that contains data
    Set cel = rg.Cells(rg.Rows.Count, 1)             'First column, last row that contains data
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)        'Last row, last column that contains data


' Put Borders around the Range selection from above
    With rg.Borders
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

    Range("A1:A2").Select
    Selection.Font.Bold = True

    Columns("A:A").Select
    Selection.ColumnWidth = 60

    Range("B1:B2").Select
    Selection.Font.Italic = True

    ActiveSheet.Move Sheets(Sheets("Summary").Index + 1)

    Columns("A:A").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Range("A6").Select
    ActiveSheet.Name = "Q1 Summary By Audit Criteria"


' Create Audit Criteria Chart
    Sheets.Add
    ActiveSheet.Select
    Cells(3, 1).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Detail!R1C1:R278C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="Q1 Audit Criteria_Graph", DefaultVersion _
        :=xlPivotTableVersion12

   With ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").PivotFields("Manager_Name")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").PivotFields("Assoc")
        .Orientation = xlPageField
        .Position = 1
    End With

    Columns("B:B").EntireColumn.AutoFit

    Range("A1:A2").Font.Bold = True

    Columns("A:A").ColumnWidth = 60

    Range("B1:B2").Font.Italic = True

    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)
    Set cel = rg.Cells(1, rg.Columns.Count)
    Set cel = rg.Cells(rg.Rows.Count, 1)
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)
    Dim cht As Chart
    ActiveSheet.Shapes.AddChart
    With ActiveSheet.ChartObjects(1).Chart
        .SetSourceData Source:=rg
        .ChartType = xlBarStacked
        .ChartStyle = 2
        .ClearToMatchStyle
        .ApplyLayout (2)
        .ChartTitle.Text = "Audit Criteria Errors"
    End With

    With ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").PivotFields("Month")
        .Orientation = xlColumnField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").PivotFields( _
        "Quality_Review_Criteria")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").AddDataField ActiveSheet.PivotTables( _
        "Q1 Audit Criteria_Graph").PivotFields("InquiryNum"), "Count of InquiryNum", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False

    On Error Resume Next

    With ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").PivotFields("Month")
        .PivotItems("April").Visible = False
        .PivotItems("May").Visible = False
        .PivotItems("June").Visible = False
        .PivotItems("July").Visible = False
        .PivotItems("August").Visible = False
        .PivotItems("September").Visible = False
        .PivotItems("October").Visible = False
        .PivotItems("November").Visible = False
        .PivotItems("December").Visible = False
    End With

    On Error GoTo 0

'    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWorkbook.ShowPivotChartActiveFields = False

    ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").DataPivotField.PivotItems( _
        "Count of InquiryNum").Caption = " "

    ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").CompactLayoutRowHeader = _
        "Audit Criteria"

    ActiveSheet.PivotTables("Q1 Audit Criteria_Graph").CompactLayoutColumnHeader = " "
    Cells.EntireColumn.AutoFit

    ActiveSheet.Move Sheets(Sheets("Summary").Index + 6)

    With Columns("A:A")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count     'This statement is sometimes needed to reset the UsedRange property
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)       'First row and column that contain data
    Set cel = rg.Cells(1, rg.Columns.Count)        'First row, last column that contains data
    Set cel = rg.Cells(rg.Rows.Count, 1)             'First column, last row that contains data
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)        'Last row, last column that contains data

    With rg.Borders
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With ActiveSheet.ChartObjects(1)
        .Left = rg.Cells(5, rg.Columns.Count + 2).Left
        .Top = rg.Cells(5, 1).Top
        .Height = 600
        .Width = 600
    End With

    ActiveSheet.Range("A6").Select
    ActiveSheet.Name = "Q1 Audit Criteria_Graph"


' Create Associate Error Chart
    Sheets.Add
    ActiveSheet.Select
    Cells(3, 1).Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Detail!R1C1:R278C8", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="", TableName:="Q1 Associate Error_Graph", DefaultVersion _
        :=xlPivotTableVersion12

    With ActiveSheet.PivotTables("Q1 Associate Error_Graph").PivotFields("Manager_Name")
        .Orientation = xlPageField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Q1 Associate Error_Graph").PivotFields("Assoc")
        .Orientation = xlPageField
        .Position = 1
    End With

    Columns("B:B").EntireColumn.AutoFit

    Range("A1:A2").Font.Bold = True

    Columns("A:A").ColumnWidth = 60

    Range("B1:B2").Font.Italic = True

    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)
    Set cel = rg.Cells(1, rg.Columns.Count)
    Set cel = rg.Cells(rg.Rows.Count, 1)
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)

    ActiveSheet.Shapes.AddChart
    With ActiveSheet.ChartObjects(1).Chart
        .SetSourceData Source:=rg
        .ChartType = xlColumnStacked
        .ChartStyle = 2
        .ClearToMatchStyle
        .ApplyLayout (2)
        .ChartArea.Interior.ColorIndex = 48
        .PlotArea.Interior.ColorIndex = 15
        .ChartTitle.Text = "Audit Criteria Errors"
    End With

    With ActiveSheet.PivotTables("Q1 Associate Error_Graph").PivotFields("Month")
        .Orientation = xlColumnField
        .Position = 1
    End With

    With ActiveSheet.PivotTables("Q1 Associate Error_Graph").PivotFields("Employee")
        .Orientation = xlRowField
        .Position = 1
    End With

    ActiveSheet.PivotTables("Q1 Associate Error_Graph").AddDataField ActiveSheet.PivotTables( _
        "Q1 Associate Error_Graph").PivotFields("InquiryNum"), "Count of InquiryNum", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False

    On Error Resume Next

    With ActiveSheet.PivotTables("Q1 Associate Error_Graph").PivotFields("Month")
        .PivotItems("April").Visible = False
        .PivotItems("May").Visible = False
        .PivotItems("June").Visible = False
        .PivotItems("July").Visible = False
        .PivotItems("August").Visible = False
        .PivotItems("September").Visible = False
        .PivotItems("October").Visible = False
        .PivotItems("November").Visible = False
        .PivotItems("December").Visible = False
    End With

    On Error GoTo 0

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWorkbook.ShowPivotChartActiveFields = False
    ActiveChart.ChartTitle.Text = "Audit Criteria Errors"

    ActiveSheet.PivotTables("Q1 Associate Error_Graph").DataPivotField.PivotItems( _
        "Count of InquiryNum").Caption = " "

    ActiveSheet.PivotTables("Q1 Associate Error_Graph").CompactLayoutRowHeader = _
        "Audit Criteria"

    ActiveSheet.PivotTables("Q1 Associate Error_Graph").CompactLayoutColumnHeader = " "
    Cells.EntireColumn.AutoFit

    ActiveSheet.Move Sheets(Sheets("Summary").Index + 8)

    With Columns("A:A")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    Set ws = ActiveSheet
    i = ws.UsedRange.Rows.Count     'This statement is sometimes needed to reset the UsedRange property
    Set rg = ws.UsedRange
    Set cel = rg.Cells(1, 1)       'First row and column that contain data
    Set cel = rg.Cells(1, rg.Columns.Count)        'First row, last column that contains data
    Set cel = rg.Cells(rg.Rows.Count, 1)             'First column, last row that contains data
    Set cel = rg.Cells(rg.Rows.Count, rg.Columns.Count)        'Last row, last column that contains data

    With rg.Borders
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With ActiveSheet.ChartObjects(1)
        .Left = rg.Cells(5, rg.Columns.Count + 2).Left
        .Top = rg.Cells(5, 1).Top
        .Height = 270
        .Width = 600
    End With

    ActiveSheet.Range("A6").Select
    ActiveSheet.Name = "Q1 Associate Error_Graph"


' remove any sheets that are not named and are blank
Application.DisplayAlerts = False

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name Like "Sheet*" Then
            ws.Delete
        End If
    Next ws

Application.DisplayAlerts = True


' Move focus back to Summary Sheet
Sheets("Summary").Select
Range("A8").Select

Application.EnableEvents = True

End Sub

Open in new window

0
Comment
Question by:gdunn59
  • 4
  • 2
8 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39956714
Hi,

Have you tried

".PivotItems("Blank").Visible = False

Open in new window

Regards
0
 
LVL 1

Author Comment

by:gdunn59
ID: 39957410
Rgonzo1971:

Yes, I mentioned it in my initial question that on line 62 of the Code I have:

   .PivotItems("(blank)").Visible = False


See my initial question.  Not sure if I have it in the correct spot for it to work or not.

Any other suggestions?

Thanks,
gdunn59
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 39996908
There doesn't seem to be anything wrong with your code though are you able to post a sample spreadsheet for me to look at?  I can't replicate your issue.  In otherwords I use the PivotItems("(blank)").Visible = False and it hides the row, even in a multidimensional table
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:gdunn59
ID: 40015639
Rob,

Because of HIPAA Laws I cannot post the data.

Can you cut/paste my code I posted and try it with some data on your end?

Thanks,
gdunn59
0
 
LVL 42

Expert Comment

by:Rob Jurd, EE MVE
ID: 40019206
I can't use your code because it references pivot tables dimensions and fields that I don't have and I don't know how they're pivoted.

Would you be able to obfuscate the data?  Even better, all I would need is an empty dataset that the pivot tables reference with just one line of sample data in each dataset.  The pivot tables' results wouldn't make much sense but the problem here may be the structure rather than the data.
0
 
LVL 1

Accepted Solution

by:
gdunn59 earned 0 total points
ID: 40042306
I ran a compact and repair and tried it again, and it worked with the original line of code that I had:

    .PivotItems("(blank)").Visible = False
0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 40055649
Because the original line of code that I already had when I posted this question, seemed to work after I ran a compact and repair.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now