Macro to clear Data in ColE one row below "Grand Total"

[howard]

I have a Pivot Table (PivotTable4) on sheet "Ageing Summary"

I have tried to write code to clear a value in Col E on row below where "Grand Total" is in Col A for Eg if Grand Total is in A7 then clear E8


Kindly amend my Code

 Sub ClearValueInColE_BelowGrandTotal()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim lastRow As Long
    Dim grandTotalRow As Long
   
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Ageing Summary")
   
    ' Set the Pivot Table
    Set pt = ws.PivotTables("PivotTable4") 
   
    ' Set the Pivot Field for "Ageing"
    Set ptField = pt.PivotFields("Ageing")
   
    ' Find the "Grand Total" item
    On Error Resume Next
    Set ptItem = ptField.PivotItems("Grand Total")
    On Error GoTo 0 ' Turn off error handling
   
    ' Check if "Grand Total" item is found
    If ptItem Is Nothing Then
        MsgBox "Error: Pivot item 'Grand Total' not found.", vbExclamation
        Exit Sub
    End If
   
    ' Get the row number of "Grand Total" in the Pivot Table
    grandTotalRow = ptItem.Position + ptItem.PivotFields(1).DataRange.Rows(1).Row
   
    ' Get the last used row in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
   
    ' Clear data in column E one row below "Grand Total" in column A
    If grandTotalRow + 1 <= lastRow Then
        ws.Cells(grandTotalRow + 1, 5).ClearContents ' Column E is column 5
    Else
        MsgBox "No data found below 'Grand Total' in column E.", vbInformation
    End If
End Sub [code]

 

[howard]

I have resolved this

Sub ClearValueInColE_BelowGrandTotal()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim grandTotalRow As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("Ageing Summary")
    
    ' Set the Pivot Table
    Set pt = ws.PivotTables("PivotTable4")
    ' Find the "Grand Total" item row number in the Pivot Table
    grandTotalRow = ws.Cells.Find("Grand Total").Row
    
    ' Clear data in column E one row below "Grand Total"
    ws.Cells(grandTotalRow + 1, "E").ClearContents ' Column E is column 5
End Sub