Home>

Thing I want to do
I want to delete the cell contents of two sheets each time I open it.
Sheet1 ⇒ Delete all
Shhet2 ⇒ Delete the last matrix from A2 (leave only the first row)

What I tried

Sub cell deletion ()
ThisWorkbook.WorkSheets ("Sheet1"). Cells.Clear
  ThisWorkbook.WorkSheets ("Sheet2"). Range ("A2", Cells (Rows.Count, 1) .End (xlUp)). EntireRow.Delete
End Sub

Symptoms
This works fine the first time, but when I try to run the Sheet2 script the second and subsequent times, I get the error "Runtime error '1004': Application definition or object definition error."

There was an explanation about the error that occurred from the second time onward in MS, but I could not understand it well even if I looked at it.
Please tell me how to resolve the error.

vba
  • Answer # 1

    In the original code, if Sheet2 is not active, you will get an error.
    Cells (Rows.Count, 1) .End (xlUp) points to the cell in the active sheet. Let's specify the sheet.

    ThisWorkbook.Worksheets ("Sheet2"). Range ("A2", ThisWorkbook.Worksheets ("Sheet2"). Cells (Rows.Count, 1) .End (xlUp)). EntireRow.Delete

  • Answer # 2

    I think that the error will not occur below.

    Sub cell deletion ()
    ThisWorkbook.WorkSheets ("Sheet1"). Cells.Clear
      ThisWorkbook.WorkSheets ("Sheet2"). Range ("A2", ThisWorkbook.WorkSheets ("Sheet2"). Cells (Rows.Count, 1) .End (xlUp)). EntireRow.Delete
    End Sub

    However, when I executed it the second time, the data in the first line of Sheet2 also disappeared.