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

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?

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
"Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
> 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?

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" wrote:

> 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
> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
> > 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?
>
>
>

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
"Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
news:D04E37DD-5C5B-4ADD-8A03-D0E143D28D85@microsoft.com...
> 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" wrote:
>
>> 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
>> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
>> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
>> > 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?
>>
>>
>>

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" wrote:

> 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
> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
> > 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?
>
>
>

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
"Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
news:D04E37DD-5C5B-4ADD-8A03-D0E143D28D85@microsoft.com...
> 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" wrote:
>
>> 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
>> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
>> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
>> > 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?
>>
>>
>>

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

"Otto Moehrbach" wrote:

> 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
> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> news:D04E37DD-5C5B-4ADD-8A03-D0E143D28D85@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> >> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>
>

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

"Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
news:E6BBC398-86B3-40E8-9BF0-C25776414824@microsoft.com...
> It appears that the target.count is also 256 when a row gets inserted.
>
> "Otto Moehrbach" wrote:
>
>> 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
>> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
>> news:D04E37DD-5C5B-4ADD-8A03-D0E143D28D85@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
>> >> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
>> >> > 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,

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
"Otto Moehrbach" wrote:

> 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
>
> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> news:E6BBC398-86B3-40E8-9BF0-C25776414824@microsoft.com...
> > It appears that the target.count is also 256 when a row gets inserted.
> >
> > "Otto Moehrbach" wrote:
> >
> >> 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
> >> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> >> news:D04E37DD-5C5B-4ADD-8A03-D0E143D28D85@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >> "Tim Patton" <TimPatton@discussions.microsoft.com> wrote in message
> >> >> news:7A95A2CA-13B9-4950-A2E9-6D7958CDE980@microsoft.com...
> >> >> > 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>