Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove empty result from pivot table

Posted on 2010-11-11
4
Medium Priority
?
475 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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

876 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