Error 1004

[Arne Hegefors]

Hi! I have a button which I use to delete ceratin rows from several sheets in
a workbook. If the button is pressed the user shall enter a date and the code
finds the rows for that date and deletes them .This used to work but now I
get "Error 1004. The Delete methd in Range class failed.". I dont know what
has gone wrong. It gives me error on the row sh_Rows(i).Delete

Public Sub deleteRow()
'deletes all rows for certain dates
Dim sh_ As Worksheet
Dim i As Long
Dim intAnswer As Integer
Dim strDate As String
Dim strPswTry As String
Dim strPsw As String

strPsw = "sombrero"

strDate = Application.InputBox(prompt:="Which date do you want to delete?",
Type:=2)
If strDate <> "Falskt" Then
strPswTry = Application.InputBox(prompt:="Enter password", Type:=2)
'If strPswTry = strPsw Then
MsgBox "Password correct."
For Each sh_ In ActiveWorkbook.Sheets
For i = sh_.Cells(65536, 1).End(xlUp).Row To 1 Step -1
If sh_.Cells(i, 1).Value = strDate Then
sh_.Rows(i).Delete
End If
Next i
Next sh_
'End If
MsgBox "Password incorrect."
End If
End Sub

 

[paul.robinson]

Hi! I have a button which I use to delete ceratin rows from several sheets in
a workbook. If the button is pressed the user shall enter a date and the code
finds the rows for that date and deletes them .This used to work but now I
get "Error 1004. The Delete methd in Range class failed.". I dont know what
has gone wrong. It gives me error on the row sh_Rows(i).Delete

Public Sub deleteRow()
'deletes all rows for certain dates
Dim sh_ As Worksheet
Dim i As Long
Dim intAnswer As Integer
Dim strDate As String
Dim strPswTry As String
Dim strPsw As String

strPsw = "sombrero"

strDate = Application.InputBox(prompt:="Which date do you want to delete?",
Type:=2)
If strDate <> "Falskt" Then
strPswTry = Application.InputBox(prompt:="Enter password", Type:=2)
'If strPswTry = strPsw Then
MsgBox "Password correct."
For Each sh_ In ActiveWorkbook.Sheets
For i = sh_.Cells(65536, 1).End(xlUp).Row To 1 Step -1
If sh_.Cells(i, 1).Value = strDate Then
sh_.Rows(i).Delete
End If
Next i
Next sh_
'End If
MsgBox "Password incorrect."
End If
End Sub

Hi
You will get this error if the sheet or cells on it are protected.
Your code works fine for me on an unprotected sheet.
regards
Paul

 

[Jay]

Hi Arne -

I get the same result as Paul. Your code works fine unless sheet is
protected and contains a matching date.