Clear column content in Excel table based on specific cell value

Question

I have the following Table in my macro-enabled excel file,

Table1:

My goal is:

Before closing this workbook, I need to clear Column D content (shown as "Column 4" in the Table1), only where column D cells value = 999. For example, looking at the screenshot above - cell content in D2 and in D4 must be cleared when "close workbook" event triggered.

Important - if user will create a new record(s) in this table, then any new cell under Column D could have 999 value too. If a new record contains cell value = 999 in the Column D - it must be cleared! Any new record where cell value = 999 in column D must be cleared!

See updated picture of what I expect -

cells D2, D4, and also D7 (after the new record created in this tbl) had 999 value and cleared:

Using the following vba code but it's not working:

     Private Sub Workbook_BeforeClose(Cancel As Boolean)

        Dim sht As Worksheet
        Set sht = ThisWorkbook.ActiveSheet

        If Worksheets("Sheet1").ListObjects("Table1").Columns(4).Value = 999 Then
           Worksheets("Sheet1").ListObjects("Table1").Columns(4).ClearContents
  
        End If

    End Sub

Answer 1

Two immediate issues:

• A ListObject does not have a Columns property, rather a ListColumns property.
• Loop* over the cells in the ListColumn.DataBodyRange rather than attempting to compare the entire column to 999.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim tbl As ListObject
    Set tbl = Me.Worksheets("Sheet1").ListObjects("Table1")
    
    Dim cell As Range
    For Each cell In tbl.ListColumns(4).DataBodyRange
        If cell.Value = 999 Then 
            cell.ClearContents
        End If
    Next
End Sub

^{* This can be made much faster using a Variant array but should be fine for a relatively small table.}

Answer 2

Clear Values in an Excel Table

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim ws As Worksheet: Set ws = Me.Worksheets("Sheet1")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1")
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Column 4") ' 4
        
    lcl.DataBodyRange.Replace 999, Empty, xlWhole
    
End Sub

EDIT

• If you want to avoid the 'save alert dialog' when the workbook was already saved you can improve with the following:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Dim IsSaved As Boolean: IsSaved = Me.Saved
    
    Dim ws As Worksheet: Set ws = Me.Worksheets("Sheet1")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("Table1")
    Dim lcl As ListColumn: Set lcl = tbl.ListColumns("Column 4") ' 4
        
    lcl.DataBodyRange.Replace 999, Empty, xlWhole
    
    If IsSaved Then Me.Save
    
End Sub