VBA，带工作表，清除表格内容

Question

我有一张要清理的表，得到 object required我的范围线上的错误。

我也尝试过其他事情，但它也倾向于清除当前页面。

Sub ClearDatabase()
    With ThisWorkbook.Worksheets("database").Select
        Range("A2:FR" & .Cells(.Rows.count, "A").End(xlUp).row).ClearContents  'adjust this if needing to clear more
    End With
End Sub

Answer 1

正确使用 With ... End With block 。

Sub ClearDatabase()
    With ThisWorkbook.Worksheets("database")
        'adjust this if needing to clear more
        .Range("A2:FR" & .Cells(.Rows.count, "A").End(xlUp).row).ClearContents  
        'alternate
        '.Range(.cells(2, "A"), .Cells(.Rows.count, "A").End(xlUp).offset(0, 173)).ClearContents
    End With
End Sub

.CurrentRegion 属性将覆盖从 A1 向外辐射的区域，直到它到达一个完全空白的行和完全空白的列。

Sub ClearDatabase()
    With ThisWorkbook.Worksheets("database")
        'offset preserves the column header labels in row 1
        .Range("A1").CurrentRegion.offset(1, 0).ClearContents  
    End With
End Sub