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)
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 Subwhen i run this macro it keeps highlighting it red "Sub Remove_Blank_From_PivotTable1()" why does it do that anyone help ?
