Excel Application-defined or object-defined error

0
votes

I'm trying to loop through all sheets in a spreadsheet and clear the contents of cells A4 to the end of the sheet.

I have it working if the spreadsheet only contains 1 sheet. If it contains more than 1 sheet, it fails when trying to clear the contents on sheet 2.

The line in error is .Range("A4", Range("A4").SpecialCells(xlLastCell)).ClearContents

When it hits that line, I get the error 'Excel Application-defined or object-defined error'

Can you see what I'm doing wrong?

    Set book = Workbooks.Open("folder-containing-excel-spreadsheets")
    For Each Sheet In book.Sheets
        With Sheet
            .Range("A4", Range("A4").SpecialCells(xlLastCell)).ClearContents
        End With
    Next
    book.Save
    book.Close
1
vbaexcel

1 Answers

2
votes

Try to change 

.Range("A4", Range("A4").SpecialCells(xlLastCell)).ClearContents

to

.Range("A4", .Range("A4").SpecialCells(xlLastCell)).ClearContents

You've missed . before Range("A4").SpecialCells(xlLastCell) and so, Range("A4").SpecialCells(xlLastCell) always tries to find last cell in first sheet