Solved

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

Posted on 2014-03-26
8
5,373 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
[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
  • 4
  • 2
8 Comments
 
LVL 50

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 43

Expert Comment

by:Rob
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 43

Expert Comment

by:Rob
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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