Remove row if cell contains #N/A ...and remove duplicates

[PivotMeThis]

I have a report that contains a lookup table, a pivot table and code/formatting for a drill down report.

Data contains 4 lookups for various items about completed projects, but not all projects are completed so there are a lot of #N/A in the data. This sheet is hidden.

The pivot table summarizes the data for completed projects by FY/Quarter and dollar amount.

There is code to format the drill down report so that when a user double clicks on the pivot table only certain data will be made available in the report.

The problem is all the #N/As that appear. I am looking for some code that would remove the row if #N/A appears in column "Q" on the drill down reports.

The other problem is the duplicates. This report lists all work orders that were written for each project. So the project may be listed multiple times. The pivot table and drill down report show project totals only so anything duplicated in column "M" should be removed so that it only appears once on the drill down reports.

Seems like a tall order. Hope someone can help me.
Thanks!

 

[sijpie]

For removing duplicate rows or rows containing #NA do a search on this forum on deleting rows.

I am not very familiar with pivot tables, I use them now and again... I am not sure, but I thought you could set certain filter parameters to for instance leave out empty rows or duplicates. but maybe I am misunderstanding: On drill-down does the user get the info through another pivot table, or from the main data table with filters?

 

[PivotMeThis]

The #N/A and duplicates do not appear in the pivot table. This occurs when the drill down report is created. The info comes from the data - the lookup tables are putting the #N/A in the data if information is not found. There is code added to remove the filters and format the drill down report. I don't want the filters in the drill down report.

 

[sijpie]

Then i sugest that you run a macro for the drilldown that makes a copy of, the data, deletes the NA rows and duplicates, and shows the result to the user.

 

[Jerry Sullivan]

Hi Rhonda, It's been too long since we shared a thread!

Below is some code that's intended to be somewhat adaptable and stand-alone for the purpose you describe.

I understand you already have some code that is triggered when a Pivot Drilldown is created; so this code will need to be integrated with that to ensure they work together.

Paste into the ThisWorkbook Code Module...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Call CheckDrillDown
End Sub

Paste into a Standard Code Module...

Sub CheckDrillDown()
    Dim tblNew As ListObject

    On Error Resume Next
    Set tblNew = ActiveSheet.ListObjects(1)
    On Error GoTo 0

    If tblNew Is Nothing Then Exit Sub

    
    Application.ScreenUpdating = False

    
    '--delete rows with #N/A in Column "Q"
    Call DeleteRowsFromTable(tblNew, sCriteria:="ISNA(RC17)")

   
    '--delete rows with duplicates in Column "M"
    Call DeleteRowsFromTable(tblNew, sCriteria:="COUNTIF(C13,RC13)>1")

    
    '--optional: Convert ListObject Table to standard Range
    tblNew.Unlist

    
    Application.ScreenUpdating = True
End Sub

Private Function DeleteRowsFromTable(tbl As ListObject, sCriteria As String)
'--deletes rows from tbl that return True to WorksheetFormula expression sCriteria

    
    With tbl.Range
        '--add temp column for criteria formulas and sorting
        With .Columns(.Columns.Count + 1)
            .FormulaR1C1 = "=if(" & sCriteria & ","""",""OK"")"
            .Value = .Value
        End With
    End With

    
    With tbl.Sort
        '--sort to make rows to delete contiguous range
        .SortFields.Clear
        .SortFields.Add Key:=tbl.Range.Columns(tbl.Range.Columns.Count), _
            SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .Header = xlYes
        .Orientation = xlTopToBottom
        .Apply
    End With

        
    With tbl.Range
        With .Columns(.Columns.Count)
            '--delete rows with blanks, if any
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
            On Error GoTo 0
            '--delete temp column
            .EntireColumn.Delete
        End With
    End With
End Function

If you want any help integrating this code with your current Drilldown functions, just post the top level procedures you are using (you don't need to post all the called procedures that do the formatting of the Drilldown just the Sub that calls it).

 

[PivotMeThis]

Thanks Jerry, I'll get back to you if I run into any snags - I'm not going to be able to try this for a few days.

Always good hearing from you
Rhonda

 

[PivotMeThis]

Hi again Jerry,

I should have known I would need your help with this.

On the pivot table worksheet I have: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

On "this workbook" I have: Private Sub Workbook_NewSheet(ByVal Sh As Object)

In the standard module I have:
Public Function Format_PT_Detail(tblNew As ListObject)
Private Function Format_Table(tbl As ListObject, rFieldFormats As Range)

All this code, I got from you

I hope I have sent the right information.

You mentioned this code is intended to be somewhat adaptable and stand-alone. Does this mean I can use it in other workbooks without the drill down code? Tweek it to suit my needs?

 

[Jerry Sullivan]

You mentioned this code is intended to be somewhat adaptable and stand-alone. Does this mean I can use it in other workbooks without the drill down code? Tweek it to suit my needs?

Yes. You could put this in another workbook with no other VBA code if all you wanted to do was delete drilldown records that met certain criteria.
You could modify the two formula examples I showed to be any formula(s) that evaluate to True on the rows to be deleted when placed in a temporary column added to the right of the table.


In integrating this latest code with your existing, the main thing to be aware of is that we don't want to convert the ListObject Table into a Standard Range until the end. That's because each of the procedures was intended to process a ListObject. So if we "Unlist" it too early, the subsequent code won't work as intended.

Here's what I'd suggest (untested).

In ThisWorkBook...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject
    On Error Resume Next
    
    Set tblNew = Cells(1).ListObject
    If tblNew Is Nothing Then Exit Sub
    Call Format_PT_Detail(tblNew)
    [B][COLOR="#0000CD"]Call CheckDrillDown[/COLOR][/B] ' <--add to your existing procedure
    Set tblNew = Nothing
End Sub

In your existing Format_PT_Detail function, delete or comment out this statement...

tblNew.Unlist   'Optional: Converts Table to Standard Range

Please let me know whether that works.

 

[PivotMeThis]

Nice! Works great.
Thanks Jerry

 

[Jerry Sullivan]

I was referencing this code from another thread and noticed that I had an error on one of the examples.


The Sub CheckDrillDown in Post #5 provides some examples of R1C1 formulas that can be passed to DeleteRowsFromTable.

The example for deleting rows with duplicates should be corrected to read:

    '--delete rows with duplicates in Column "M"
    Call DeleteRowsFromTable(tblNew, sCriteria:="COUNTIF(R2C13:RC13,RC13)>1")