here is the current code i have :

Sub Remove_Blank_From_PivotTable1()

' The purpose of this macro is to remove the word (blank) from apprearing
' on any pivot table report.'

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 = "David Edwards" ' 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)
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

when i run this macro it keeps highlighting it red "Sub Remove_Blank_From_PivotTable1()" why does it do that anyone help ?

Nice commenting and layout of code. Makes it easier for us to read.

If I read this correctly, the sub is searching fro blank, if not found it still wants to do something, hence the error.

Add some error trapping so the sub can be exited if an error was raised.

This article has been dead for over six months. Start a new discussion instead.