Solved

Remove empty result from pivot table

Posted on 2010-11-11
4
465 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect

Thanks for your help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

13 Experts available now in Live!

Get 1:1 Help Now