Replacing Pivot Table with Values to Filter and Delete Rows

Question

I am trying to build a spreadsheet around some data coming from a pivot table, but I cannot filter and delete rows.

I've tried .Value = .Value for the table but I am getting the error message

"We can't make this change for the selected cells because it will affect a PivotTable..." (Run-time error '1004')

    With ws
        FinalRow = .Cells(Cells.Rows.Count, "B").End(xlUp).Row
        With .Range("B1:C" & FinalRow)'Pivot table range
            .Value = .Value
        End With

        For iCntr = FinalRow To 1 Step -1
            If Rows(iCntr).Hidden Then
                Rows(iCntr).EntireRow.Delete
            End If
        Next iCntr
    End With
End With

CLR CLR · Accepted Answer · 2019-07-25T14:40:11

I don't think you can just .Value the range like that. You need to remove the pivot - by perhaps storing the data, clearing the range and then re-writing the data.

Try this:

Dim tmp As Variant
With ws
    FinalRow = .Cells(Cells.Rows.Count, "B").End(xlUp).Row
    With .Range("B1:C" & FinalRow) 'Pivot table range
        tmp = .Value
        .ClearContents
        .Value = tmp
    End With
End With

Incidentally, if you've 'hidden' items in the pivot, they're removed from the table, not actually hidden - so I suspect there will be no need to delete afterwards..

Replacing Pivot Table with Values to Filter and Delete Rows

1 Answers