VBA: conflict of delete row command and a module

[6656]

Hi,
I need help on my Exce2000 VBA to be fixed.
When I use Delete-Row command on the menu, I got an error message " run-time error 1004, application or object defined error". Debug points to the third line(2nd IF...) of my script below. There is no error if delete cells or whole columns. Below code works fine in Excel97/95.

********* My script **************;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row <> 1 Then
If Target.Text <> &quot;&quot; And IsEmpty(Target.Offset(0, 17).Value) Then
Application.EnableEvents = False
Target.Offset(0, 17).Value = &quot;from:&quot;
Application.EnableEvents = True
ElseIf Target.Text = &quot;&quot; Then
Application.EnableEvents = False
Target.Offset(0, 12).ClearContents 'Value = &quot;&quot;
Application.EnableEvents = True
End If
End If
End Sub
*************** end ***********;

Thanks,
Mike

 

[KenWright]

You can use 'On error resume next' to trap it.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 1 And Target.Row <> 1 Then
If Target.Text <> &quot;&quot; And IsEmpty(Target.Offset(0, 17).Value) Then
Application.EnableEvents = False
Target.Offset(0, 17).Value = &quot;from:&quot;
Application.EnableEvents = True
ElseIf Target.Text = &quot;&quot; Then
Application.EnableEvents = False
Target.Offset(0, 12).ClearContents 'Value = &quot;&quot;
Application.EnableEvents = True
End If
End If
End Sub

Regards
Ken..............

 

[6656]

Thanks Ken,
It works. However it takes 4 minits to delete 4000 rows. Do you have any solution.

Regards,
Mike

 

[IfThenElvis]

If you are looking for an automated way to delete rows then try this (modify as required. It's best when deleting rows via VBA to work up from the bottom:

Sub del_rows()
lastrow = Cells(65536, 1).End(xlUp).Row
For c = lastrow To 1 Step -1
If Cells(c, 1).Value = &quot;aa&quot; Then Cells(c, 1).EntireRow.Delete 'note: or whatever your condition is...
Next c
End Sub