How to capture deletions of row(s) "event"

[michellepace]

Hello there,

Requirement: I would like to capture the "event" of rows being deleted in a sheet, in particular I need to capture the first and last row being deleted.

I'm really struggling with this as I have been at it for the last two days. I have tried to do this with the below code but it does not work consistently - sometimes the (2) code is reached and sometimes not. I'm sure there must be a better way to satisfy my requirement. Could anyone please provide a suggestion?

Please Login or Register  to view this content.

Thanks in advance,
Michelle

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

The penultimate two lines seem to be necessary to get rid of a thick black border that gets left otherwise.

[TMS]

Hi Richard

might be missing something but I encountered a couple of problems.

The first is that, if I select whole rows and press the Delete key, it errors out with Run Time error 1004. If I use the Delete icon on the Home ribbon, it's OK.

This can be overcome by:

Please Login or Register  to view this content.

But, bigger problem, I can't actually type anything into the spreadsheet. It just tells me the row and undoes the typing

Regards, TMS

[TMS]

This might work for you. It depends on column A being fully populated, that is, a key field that will have data in every row.

Please Login or Register  to view this content.

Note that it is possible to Undo any changes as event handlers are storing or reporting but not changing anything.


Regards, TMS

[Richard Buttrey]

Morning Trevor,

That's strange since it's still working for me here. See attached video.

Oh, even a short 7 second video @ 300Kb is seemingly too big for the forum

Here's what I did.

Select a few rows by clicking in the row numbers area, move to the ribbon Cells area and hit delete, or indeed hit the delete key.

[TMS]

That's why I said I might be missing something . As your code stands, if I use the Delete key, I get an error 1004 on the Undo. If I use the Delete icon, I'm okay. But that's fixable. However, if I just type anything the Undo undoes the typing but then deletes the row

So, if I switch off the event handler for a moment and populate column A with some data, then switch on the event handler, if I type anywhere, the whole row will be deleted. That's because, regardless of what the change is, you have:

Please Login or Register  to view this content.

I'm thinking that can't be right

[Richard Buttrey]

...sorry for the confusion

I meant the code to be the mechanism for deleting the selected rows. I hadn't considered other changes like adding values to the sheet. But this could be overcome by wrapping the code in an

Please Login or Register  to view this content.

[TMS]

OK, on same page

[Richard Buttrey]

Excellent!

FYI I'm now 7 months and counting on that other little local difficulty at LJLA with VCS. Everything is quite on the western front since my first (and hopefully last) communication with their DR Co.

[TMS]

Ah, I never got as far as being handed off to DR. Did you know VCS actually pay LJLA as opposed to what you might have expected?

[michellepace]

Hi TMS and Richard,

Thank-you both for your replies, incredibly useful! It took me a while to work through it line by line and finally I understand the solution.

Thank-you both for taking the time to answer my question.

Michelle

[TMS]

You're welcome. Thanks for the rep.

[Richard Buttrey]

My pleasure and thanks for the rep.