Solved

Remove empty result from pivot table

Posted on 2010-11-11
4
469 Views
Last Modified: 2012-06-22
Hello all.

I would like to fix this macro when creating my pivot table.

I want to remove the empty result.

Is that possible?

Thanks again for your help.
If Application.Worksheets("Fake Carrier").PivotTables.Count > 0 Then Exit Sub

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "=TableDetail", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="'Fake Carrier'!R4C1", TableName:="Tableau croisé dynamique2", _
        DefaultVersion:=xlPivotTableVersion12
        
With Application.Worksheets("Fake Carrier").PivotTables("Tableau croisé dynamique2")
    With .PivotFields("TO_LOC_ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("USER_ID")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .PivotFields("CARRIER_ID")
        .Orientation = xlColumnField
        .Position = 1
    End With
    .AddDataField .PivotFields("ID"), "Count of ID", xlCount
    .TableStyle2 = "PivotStyleMedium8"
    .RowAxisLayout xlTabularRow
End With

Sheets("Fake Carrier").Select
   Sheets("Fake Carrier").Columns("A:A").ColumnWidth = 30
   Sheets("Fake Carrier").Columns("b:b").ColumnWidth = 20
   Sheets("Fake Carrier").Columns("c:c").ColumnWidth = 20
   Sheets("Fake Carrier").Columns("d:d").ColumnWidth = 20

Open in new window

0
Comment
Question by:Wilder1626
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:sijpie
ID: 34118342
What you could do is run down the table and if an empty cell is encountered delete the row. I don't know in which column your empty data resides, but say it is in the 2nd column, then you could do something like:

dim RwC as long

for RwC = .tablerange1.rows to 0 step -1
   if  .tablerange1.offset(RwC,1).value = vbNullstring then
       .tablerange1.offset(RwC,1).entirerow.delete
   end if
next RxC

Open in new window


In this example I am running up the table form the bottom, to not upset my row count when delteting lines
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 34120063
So there is now way to just remove the option EMPTY in the macro?

empty.JPG
0
 
LVL 6

Accepted Solution

by:
sijpie earned 500 total points
ID: 34120730
Of course:
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("month")
        .PivotItems("(blank)").Visible = False
    End With

Open in new window


Hope that helps!
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 34121290
Perfect

Thanks for your help
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

808 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