清除Excel表格（ListObject）列

主要

Sub NewHours()
'
' New_Hours Macro
' Moves down a sheet and removes previous hours
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    On Error GoTo ClearError
    
    With ActiveSheet.Next
        If ClearBetweenTableColumns(.ListObjects(1), "Sunday", "Saturday") Then
            Application.Goto .Range("E9")
        End If
    End With

ProcExit:
    Exit Sub
ClearError:
    'Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub

• 方法 *

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Clears the contents between two columns of an Excel table.
'               Returns a boolean indicating whether it was successful.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function ClearBetweenTableColumns( _
    ByVal Table As ListObject, _
    ByVal StartColumnID As Variant, _
    ByVal EndColumnID As Variant) _
As Boolean
    On Error GoTo ClearError

    With Table
        Dim sCol As Long: sCol = .ListColumns(StartColumnID).Index
        Dim eCol As Long: eCol = .ListColumns(EndColumnID).Index
        Dim cCount As Long: cCount = eCol - sCol + 1
        .DataBodyRange.Resize(, cCount).Offset(, sCol - 1).ClearContents
    End With

    ClearBetweenTableColumns = True

ProcExit:
    Exit Function
ClearError:
    'Debug.Print "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Function

惊喜（主要分析）

Sub NewHoursEDU()

    If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
    If ActiveSheet.Index = ActiveSheet.Parent.Sheets.Count Then Exit Sub ' last

    Dim sh As Object: Set sh = ActiveSheet.Next
    If Not TypeOf sh Is Worksheet Then Exit Sub ' not a worksheet
    If sh.ListObjects.Count = 0 Then Exit Sub ' no table

    If ClearBetweenTableColumns(sh.ListObjects(1), "Sunday", "Saturday") Then
        Application.Goto sh.Range("E9")
    'Else ' an error occurred in the called procedure; do nothing!?
    End If

End Sub

你可以用这个作为一个很好的起点来学习“真正的”命令（不是宏记录的命令）：

Public Sub clearHours()

Dim ws As Worksheet
Dim lo As ListObject
Dim columnSunday As Long, columnSaturday As Long
Dim rgToClear As Range

For Each ws In ThisWorkbook.Worksheets
    For Each lo In ws.ListObjects
        With lo
            columnSunday = .ListColumns("Sunday").Index
            columnSaturday = lo.ListColumns("Saturday").Index
            
            Set rgToClear = .ListColumns(columnSunday).DataBodyRange.Resize(, columnSaturday - columnSunday + 1)
            rgToClear.ClearContents
            
        End With
    Next
Next

End Sub

代码检查每个工作表-以及每个工作表上的每个表（= listobject）。
然后它获取Sunday和Saturday的列索引，从中检索要清除的范围（= DataBodyRange，因为您只想清除数据部分-而不是我假设的标题）
最后一步是清除范围的内容。
（附加提示：阅读How to avoid using select和How to avoid copy/paste会给你给予更多的见解。）