Link to home
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

Remove empty result from pivot table

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

Avatar of sijpie
sijpie
Flag of Germany image

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
Avatar of Wilder1626

ASKER

So there is now way to just remove the option EMPTY in the macro?

empty.JPG
ASKER CERTIFIED SOLUTION
Avatar of sijpie
sijpie
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Perfect

Thanks for your help