Detecting when a user deletes a row

[Wescotte]

I have a script working using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

so I can format various cells after the user has entered data...

However when a user deletes an entire row the Worksheet_Change() and
since no value exist in each cell in that row (because it was just
deleted) my application resets to the default values. Basically
repopulating the row so it can never actually be deleted.

Now, my default values are pretty much "" so the row appears to be
empty to the user however say I had 500 rows and I erased 400 on the
bottom. If I go to print it will spit out quite a few more empty pages.


Now, one solution is to simply not allow for empty rows during the
printing process just erase them. But I really want the user to have
the ability to insert their own whitespace to make things easier to
read.

Really what I believe I need to do is figure out how detect when the
user is doing a right click on a row and clicking delete. Instead of
calling Worksheet_Change() perform the actual removal of the row.

Any idea how to do this?

[Jim Rech]

If the issue is the print area being bad you might try resetting it in the
before print event to the range with data:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("A1", Cells(Cells.Find("*", Range("A1"), , , _
xlByRows, xlPrevious).Row, Cells.Find( _
"*", Range("A1"), , , xlByColumns, xlPrevious) _
.Column)).Name = "Print_area"
End Sub

This code goes in the ThisWorkbook module. As it is now it will fire for
any worksheet in the workbook. If that's an issue you could add code to
check that the right sheet is active.

--
Jim
"Wescotte" <[email protected]> wrote in message
news:[email protected]...
|I have a script working using
|
| Private Sub Worksheet_Change(ByVal Target As Excel.Range)
|
| so I can format various cells after the user has entered data...
|
| However when a user deletes an entire row the Worksheet_Change() and
| since no value exist in each cell in that row (because it was just
| deleted) my application resets to the default values. Basically
| repopulating the row so it can never actually be deleted.
|
| Now, my default values are pretty much "" so the row appears to be
| empty to the user however say I had 500 rows and I erased 400 on the
| bottom. If I go to print it will spit out quite a few more empty pages.
|
|
| Now, one solution is to simply not allow for empty rows during the
| printing process just erase them. But I really want the user to have
| the ability to insert their own whitespace to make things easier to
| read.
|
| Really what I believe I need to do is figure out how detect when the
| user is doing a right click on a row and clicking delete. Instead of
| calling Worksheet_Change() perform the actual removal of the row.
|
| Any idea how to do this?
|

[Wescotte]

Adding the check during print sort of works..

Yes, if they print it it will be fine however say a user decides to
delete 100+ rows the system preforms a Worksheet_Change() for each
cell.. It can lag up to 15 seconds to completely erase the rows because
it's attempting to format each cell as it erases it.

This is the same case for a paste.. In a paste I can accept the lag
because there is no way around it but when I'm simply deleting rows
there is no reason to attempt to format them correctly as I erase them.

Really, I need to find a way to determine when an entire row is being
removed from the sheet.

[wjewell]

Did you ever find out how to do this?

Thanks,

wjewell


"Wescotte" wrote:

> Adding the check during print sort of works..
>
> Yes, if they print it it will be fine however say a user decides to
> delete 100+ rows the system preforms a Worksheet_Change() for each
> cell.. It can lag up to 15 seconds to completely erase the rows because
> it's attempting to format each cell as it erases it.
>
> This is the same case for a paste.. In a paste I can accept the lag
> because there is no way around it but when I'm simply deleting rows
> there is no reason to attempt to format them correctly as I erase them.
>
> Really, I need to find a way to determine when an entire row is being
> removed from the sheet.
>
>

[Wescotte]

Nope...

[Bird]

If anyone has some fresh insight on this issue, I too would like to know in “Worksheet_Change” if a row has been deleted by the user. My intuitive misconception looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Rows(Target.Row).Delete Then
‘
‘
‘

Thgank you

[Wescotte]

Does that solution actually work? I'm still looking for a way to detect
if a Change is actually a row being deleted.

[Bird]

No. I wish it did. Makes sense to me. but no it doesn't work.