Solution for removing (blank) from your pivot tables
Hey all,
I was having the problem with the (blank) ruining my pivot tables and created a macro to take care of the issue. In a nutshell, my code will run through your pivot table and change the color of the cell containing the (blank) to white, making it invisible. Please make sure you run the script after every time you update your pivot table to ensure that cells that should be white are and cells that shouldn't aren't.
Enjoy
Sub Remove_Blank_From_PivotTable()
' The purpose of this macro is to remove the word (blank) from apprearing
' on any pivot table report. This code is completely generic and requires only
' the absolute minimum amount of intervention from the user
' Instructions: Copy and paste this code into the VBA editor from excel into a module attached
' to the spreadsheet that contains the pivot table you'd like to clean up
' On line 33 of the code there is a variable that is used to store the name of the spreadsheet
' that contains the pivot table. Simply change the name and run the macro
' Copyright: Benjamin Crudo, August 12, 2009
' Please send all feedback to (e-mail address removed)
' This software is free to use and redistribute by anyone who wishes to do so
' If you require more help with excel or other programming needs
' at your place of business please contact me via email for my rates
' Copyright: Benjamin Crudo, August 12, 2009
Dim counter As Integer ' setting up a counter to loop through the worksheet
Dim lastRow As Integer, lastColumn As Integer ' creating variables to store the last
' rows and column that contain data on the worksheet to limit the loop
Dim WorksheetWithPivot As String ' declaring a string to store
' the name of the sheet containing the pivot table
Dim wb As Workbook
Dim ws As Worksheet ' Declaring a Worksheet variable to use to
' access the worksheet with the pivot
'____________________________________________________________________________________________________________________
WorksheetWithPivot = "Master Pivot" ' enter the name of the worksheet that contains the pivot table here
' replace the words "Master Pivot" with the name fo your sheet with your pivot
'____________________________________________________________________________________________________________________
Set wb = ThisWorkbook ' setting the workbook object to this workbook
' note, if you wanted to run this script on another excel file
' you would change the workbook and worksheets paramters, or
' you could just copy and paste this code into another workbook VBA module
Set ws = wb.Worksheets(WorksheetWithPivot) ' setting the Worksheet object
i = 1
j = 1
lastRow = ws.UsedRange.Rows.Count ' locating the last row used on the sheet
lastColumn = ws.UsedRange.Columns.Count ' locating the last column used on the sheet
Do While i <= lastRow ' loop through each row that contains data
Do While j <= lastColumn ' loop through all of the columns in each row
If ws.Cells(i, j) = "(blank)" Then ' if there is a blank in the current cell make it's text white
ws.Cells(i, j).Font.ColorIndex = 2 ' make the text white if the cell contains a (blank)
Else
ws.Cells(i, j).Font.ColorIndex = 1 ' Ensure that the text is Black if there is no (blank) in the cell
End If ' end the if statement
j = j + 1 ' increment the column
Loop ' loop through the columns
i = i + 1 ' increment the row
j = 1 ' reset the column counter back to 1 (the beginning)
Loop ' loop through the rows
End Sub
