macro to delete the positive value from pivot table

Hi Team,

Could someone please help me to give an hint how to delete the row from an pivot table.

In brief we have an excel in which there is huge data sheets of pivots.

There is an Reconciled balances in column F .we have subtotals for that if the reconciled balance is "0" at subtotal there will be an positive value in the same for ex:-1 and +1 that will give the Reconciled balance as "0".

I just need an formulae or an macro for deleting that positive value ie (+1) above the sub total.

Any help is much appreiciated.

Thanks & Regards,
Shekar goud.

Wouldn't it be better to remove the negatives from the table generating the pivot?

any luck on the above please.

Hi Bob,

thanks for your intrest.

sorry we can't remove the negative values before making an pivot.

regards,
Shekar.

See if this helps

Public Sub Test()
Dim cell As Range
Dim fScreen As Boolean
Dim fCalc As Long

    fScreen = Application.ScreenUpdating
    fCalc = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each cell In ActiveSheet.UsedRange
    
        If cell.Value < 0 Then cell.Value = 0
    Next cell
    
    Application.ScreenUpdating = fScreen
    Application.Calculation = fCalc
    Application.Calculation = xlCalculationManual

End Sub

Hi Bob,

thanks for your help again

But unfortunatley this macro is turning all the negative values to "0".Infact iam looking to delete the row which is positive in the subtotal where the Reconciled Balances is zero.

Regards,
Shekar.

Originally Posted by shekar goud

Hi Bob,

thanks for your help again

But unfortunatley this macro is turning all the negative values to "0".Infact iam looking to delete the row which is positive in the subtotal where the Reconciled Balances is zero.

Regards,
Shekar.

The problem with deleting rows is that some of te labels will get deleted. So are you saying you want the positive amounts to not count in the subtotal.

Hi Shijesh,

Thanks for the help

Unfortunately this will not help to sort out my problem.

Basically in the pivot we have subtotals for all the trades and in the subtotal i have -1 and +1 it would be zero in other column reconciled balance if the Reconcile balance is zero then it should delete the row in sub total which is positive value (+1).

hopes this is would make understand my problem i have many sheets and many "0" like this it is taking more than an hour in a day.

kindly help me.

Regards,
Shekar.