pivot table 0 totals missed up

Question

im having this pivot here, it's showing the ones with "0" total as E-15

not quite sure if it's my code? or the Excel itself?

but even when i do it manually its giving me this scientific notation thing!

however, my goal is to get the ones with sum = 0 and delete them from the master table

should i use something else other than pivot ?

' ' ' ' '

my code

``

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ACCL!R1C1:R1048576C50", Version:=7).CreatePivotTable TableDestination:= _
    newSheet.Name & "!R3C1", TableName:="PivotTable2", DefaultVersion:=7
Sheets(newSheet.Name).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable2")
    .ColumnGrand = True
    .HasAutoFormat = True
    .DisplayErrorString = False
    .DisplayNullString = True
    .EnableDrilldown = True
    .ErrorString = ""
    .MergeLabels = False
    .NullString = ""
    .PageFieldOrder = 2
    .PageFieldWrapCount = 0
    .PreserveFormatting = True
    .RowGrand = True
    .SaveData = True
    .PrintTitles = False
    .RepeatItemsOnEachPrintedPage = True
    .TotalsAnnotation = False
    .CompactRowIndent = 1
    .InGridDropZones = False
    .DisplayFieldCaptions = True
    .DisplayMemberPropertyTooltips = False
    .DisplayContextTooltips = True
    .ShowDrillIndicators = True
    .PrintDrillIndicators = False
    .AllowMultipleFilters = False
    .SortUsingCustomLists = True
    .FieldListSortAscending = False
    .ShowValuesRow = False
    .CalculatedMembersInFilters = False
    .RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("PivotTable2").PivotCache
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable2").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Doc Number")
    .Orientation = xlRowField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("LT 1 Amount"), "Sum of LT 1 Amount", xlSum
Columns("A:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Range("A3").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlToRight)).Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
    
    
    
    Sheets("Dubs po 0 inv total").Select
Sheets("Dubs po 0 inv total").Name = "0 inv total"
Sheets(newSheet.Name).Select
Sheets(newSheet.Name).Name = "0 DOC total"

Sheets("0 DOC total").Move Before:=Sheets(3)
Sheets("ACCL").Select

Sheets("ACCL").Select Range("A1").Select

End Sub

I think you should round all your raw data to two decimal point after divided, else when the decimal is more than 3, it will display as xxE-15 as per your image. — Kin Siang
Try format your pivot table total and see, if rounding was not done for all data, then you might see 0, 0.00, (0.00), although all of them should be 0 only.. — Kin Siang

kamal_hamad kamal_hamad · Accepted Answer · 2021-06-20T08:14:21

so i ended up rounding it in the code itself instead or rounding in the sheet, used the round function and it worked just fine!

rnd = Round(.Value, 2)

                If rnd <> "0" Or rnd = "" Then .EntireRow.Delete

pivot table 0 totals missed up

1 Answers