Delete data from all tables but only where column header is equal to number-CodePudding

I have VBA below which loop through all sheets in an Excel workbook and clears all data out of each Table.

However, I'd like it amend it so it only clears data from columns where the table column header is a number.

For example, in the below table, all the P AND G values would be deleted, but the names would be left alone.

Name  1   2   3   4
Ben   P       G   G
Lucy  G   P   G

I'm struggling to come up with a solution. Right now my code would clear the names as well.

Sub Clear_Tables()

    Dim TableToCheck As ListObject
    Dim WS_Count As Integer
    Dim I As Integer
    WS_Count = ActiveWorkbook.Worksheets.Count

    Result = MsgBox("This will clear data from all tables." & vbNewLine & "Are you sure you wish to continue?", vbYesNo   vbInformation)
  
    If Result = vbYes Then
        For I = 1 To WS_Count
            For Each TableToCheck In ActiveWorkbook.Worksheets(I).ListObjects
                If Not (TableToCheck.DataBodyRange Is Nothing) Then
                    TableToCheck.DataBodyRange.ClearContents
                End If
            Next TableToCheck
        Next I
    End If
End Sub

CodePudding user response：

You can iterate over the listcolumns and check if their name = column header isnumeric.

Sub Clear_Tables()
    
    If MsgBox("This will clear data from all tables." & vbNewLine & "Are you sure you wish to continue?", vbYesNo   vbInformation) = vbNo Then
        Exit Sub
    End If
    
    Dim ws As Worksheet
    Dim TableToCheck As ListObject
    Dim lc As ListColumn
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each TableToCheck In ws.ListObjects
            If Not TableToCheck.DataBodyRange Is Nothing Then
                For Each lc In TableToCheck.ListColumns
                    If IsNumeric(lc.Name) Then
                       lc.DataBodyRange.ClearContents
                    End If
                Next
            End If
        Next
    Next
    
    MsgBox "Done", vbInformation

End Sub

CodePudding user response：

Get last row
loop through column header
if column header = IsNumeric value then Range((column header).Row 1 & last row).ClearContents

may this way help you