hide blank cell pivot table

[verisme]

hi,i've got this problem, i'm using pivot table and i want to erase the blank cell that come out with pivot table and replace it with number 0 or -, how can i do that ? i've be try to do this by go to the table option and in the empty cells check list i've put 0 or - but the pivot table still come out "blank",

[Palmetto]

You haven't explained what you're trying to accomplish, so the attached is simply a guess.

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Something like "Hide blanks in PivotTable" would be better

[verisme]

no, from the sample that you gave me it would be changing my pivot table report, what i'm trying to do is, i want that "blank" cells that come out from my pivot table automatically change into 0 or empty not "blank" and without changing the source of the data that have empty cells.

i try to go to pivot tabe option and check the "for empty cells show : 0" but it doesn't work, as shown in the picture.

[DonkeyOte]

I think Palmetto was simply making the point that if the values in your Row Fields are Numeric you should store them in the Data Field and use a simple Number Format (as per his sample).

If the source values can not be changed and said values are non-numeric then I guess you could use VBA

Sub Example()
Dim rngRow As Range, wsPT As Worksheet
Set wsPT = Sheets("Sheet1")
With wsPT.PivotTables("PivotTable4")
    For Each rngRow In .DataBodyRange.Rows
        With wsPT.Cells(rngRow.Row, .TableRange1.Column).Resize(, .RowFields.Count)
            .Replace "(blank)", " ", xlWhole
        End With
    Next rngRow
End With
Set wsPT = Nothing
End Sub

The point is basically that a Pivot Item can not be Null / Blank so in the above the "(blank)" text string is replaced with a space.
(remember to change the PT specifics in the code to match your requirements - ie sheet name, pivot table name)

You could equally apply Conditional Formatting etc to set font to white etc etc...

(I remained convinced there's a more elegant method than the above)

[royUK]

Thanks for taking the time to read and comply with the rule .

[royUK]

Try this code

Option Explicit

Sub PT_RemoveBlanks()
    Dim pTbl As PivotTable
    Dim pFld As PivotField
    Dim pItem As PivotItem
    Dim aSht As Worksheet

    On Error GoTo proc_exit

    Set aSht = ActiveSheet
    Application.ScreenUpdating = False
    With aSht
        'check for PivotTable(s)
        If .PivotTables.Count > 0 Then

            For Each pTbl In .PivotTables
                For Each pFld In pTbl.PivotFields
                    For Each pItem In pFld.PivotItems
                        If pItem.Name = "(blank)" Then pItem.Name = " "
                    Next pItem
                Next pFld
            Next pTbl
        Else: MsgBox "There are no PivotTables on the " & aSht.Name
        End If
    End With
proc_end:
    Set pItem = Nothing
    Set pFld = Nothing
    Set pTbl = Nothing
    Set aSht = Nothing
    Application.ScreenUpdating = True
    Exit Sub

proc_exit:
    GoTo proc_end

End Sub

Copy the example
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste
To run the Excel macro:

Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

[verisme]

thanks for all of your answer guys !

[royUK]

Your post does not comply with Rule 9 of our Forum RULES. If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit. And please never edit a thread in which someone else has responded.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save