VBA, With Sheet, ClearContents of table

Question

I have a table I would like to clear, getting object required error on my range line.

I also have tried other things but it tends to also clear the current page im on.

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

In addition to [Jeeped's answer](https://stackoverflow.com/a/52301742/2344413), learn how to [avoid using .Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — FreeMan, Sep 12 '18 at 19:02

score 2 · Accepted Answer · 2018-09-12T19:22:02.827

2

Use the With ... End With block properly.

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

The .CurrentRegion property will cover the area radiating out from A1 until it reaches a full blank row and fully blank column.

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

edited Sep 12 '18 at 19:22

answered Sep 12 '18 at 18:57

thank you! What was I doing wrong? I see I used `Range` without the `.` . Anything else? – excelguy Sep 12 '18 at 19:35
You had added .Select on the end of the With statement. – Sep 12 '18 at 19:38

VBA, With Sheet, ClearContents of table

1 Answers1