In the worksheet_change event, how do I determine what occured?

[Guest]

In the worksheet_change event, I'm trying to determine if a row has been
deleted. Is there a way to determine what event occurred?

 

[Otto Moehrbach]

Using the Worksheet_Change event like this:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address(0, 0)
End Sub
Produces the row number of the row that was deleted. For instance, if you
delete row 5, the MsgBox will display "5:5" without the quotes. This works
whether or not the row was a blank row. Does that help? HTH Otto

 

[Guest]

Well, that kind of helps. I can't tell yet if a row has been deleted or
inserted. It gets me closer, though, so I appreciate your response!

 

[Otto Moehrbach]

The following macro will produce "256" if a row has been deleted.
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Count
End Sub
HTH Otto

 

[Guest]

It appears that the target.count is also 256 when a row gets inserted.

 

[Otto Moehrbach]

Tim
Yes, it is.
I take it that you want to know when a row is inserted and when a row is
deleted. Not knowing what your data looks like, it's not easy to nail it
down. But one idea that might work for you is to count the number of
occupied cells in the row that was changed. The Target row is the new blank
row if a row is added, or the row immediately below the row that was
deleted.
If you know that the row deleted had some data above and below it, then the
count would be greater that 1. This would tell you that a row was deleted.
Whereas, the count if the row was added will always be zero (0). The only
problem you would have is if the row deleted had an empty row immediately
below it. The count then would also be zero (0). Perhaps you can come up
with some way to check if the last row is gone
The count of occupied cells is:
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Application.CountA(Target)
End Sub

Another idea is for the code to first find out the number of occupied
rows after the user's action, then invoke the Undo feature to Undo what the
user did. Then the code can find out the number of occupied rows after the
Undo. This gives you the number of occupied rows before and after the
user's action. HTH Otto

 

[Guest]

Otto,

Thanks for your posts to my question about the worksheet_change_event. I
sure appreciate you taking the time to respond! I might be able to use your
latest recommendation but I probably won’t reply any more to the post.

Thanks again for your time

Take care,

Tim