CODEWITHSUNDEEP
vbaexcel
excelguy
excelguy

Reputation: 1624

VBA, With Sheet, ClearContents of table

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

Upvotes: 1

Views: 659

Answers (1)

user4039065
user4039065

Reputation:

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

Upvotes: 2

Related Questions