Macro that runs when entire rows are deleted?

[Pinball]

Is there a macro that can run if it detects that a user deletes one or more entire rows? Something like the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If any entire row is deleted then
<rest of macro>
End If
End Sub

 

[Colo]

Hi Pinball.
As you maybe know, excel does not have such an event.
So, I thought anothe way using calculate event.
Pls, try this and hope this help.
Before you try this you need to de-active activesheet, and re-active the sheet.
(May be you will not be able to understand what I want to say, because of my bad English...)

<pre>
Private Sub Worksheet_Activate()
Cells(Rows.Count, Columns.Count).Formula = "=A1"
End Sub

Private Sub Worksheet_Calculate()
Dim strMsg As String
If IsEmpty(Cells(Rows.Count, Columns.Count)) Then
Application.ScreenUpdating = False
Rows(Rows.Count).Clear
Columns(Columns.Count).Clear
Cells(Rows.Count, Columns.Count).Formula = "=A1"
With Selection
If .Columns.Count = Columns.Count Then strMsg = "Rows "
If .Rows.Count = Rows.Count Then strMsg = "Columns "
End With
Application.ScreenUpdating = True
Call ShowMsgBox(strMsg)
End If
End Sub

Private Sub ShowMsgBox(str)
MsgBox str & "were deleted"
End Sub

</pre>

 

[Pinball]

Colo, Thanks for your response.
What are your thoughts towards my beginner's code below to accomplish my goal?
It seems to work..the only tiny disadvantage is that after deleting row(s) the user has to click on any cell for the macro to run (but I can certainly live with that!).
Question: Is it possible to revise this code so that I do not need to store the p value in a cell, but store it in memory instead?

Thanks.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim p As Integer
p = ActiveSheet.UsedRange.Rows.Count
If p < Range("D1").Value Then
'Following line is a test formula
Range("a1").Formula = 1 + p
End If
Range("D1").Formula = p
End Sub

 

[Legacy 139]

Alternatively, you could store it in a separate hidden worksheet.

 

[Colo]

Hi again.
-- quote-----------------------------
but store it in memory instead?
-------------------------------------

I guess, if you use public Variable.
But the best way is store it in a separate hidden worksheet as Tikas wrote.


By the way, if your version of xl is XL2000, try another way I thought.
Please paste this into sheet module.

<pre>

Dim R_Count As Long
Dim C_Count As Long

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strMsg As String
If UsedRange.Rows.Count < R_Count Then strMsg = "Rows"
If UsedRange.Columns.Count < C_Count Then strMsg = "Columns"
If strMsg <> "" Then
MsgBox Target.Address & strMsg & " was deleted"
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
R_Count = UsedRange.Rows.Count
C_Count = UsedRange.Columns.Count
End Sub

</pre>

 

[Colo]

Hi Pinball, Are you still watching here?
Here is another way.

<pre>
Option Explicit

Sub EventHack()
Dim lngId As Long
Dim CtrlCbc As CommandBarControl
Dim CtrlCbcRet As CommandBarControls

Set CtrlCbcRet = CommandBars.FindControls(ID:=478)
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = "DeletionMenuRouting"
Next CtrlCbc

Set CtrlCbcRet = CommandBars.FindControls(ID:=293)
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = "LineDeletionEventProcessingProc"
Next CtrlCbc
End Sub

Sub EventReset()
Dim lngId As Long
Dim CtrlCbc As CommandBarControl
Dim CtrlCbcRet As CommandBarControls

Set CtrlCbcRet = CommandBars.FindControls(ID:=478)
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = ""
Next CtrlCbc

Set CtrlCbcRet = CommandBars.FindControls(ID:=293)
For Each CtrlCbc In CtrlCbcRet
CtrlCbc.OnAction = ""
Next CtrlCbc
End Sub


Private Sub DeletionMenuRouting()
If Not TypeOf Selection Is Range Then Exit Sub
If Selection.Address = Selection.EntireRow.Address Then
Call LineDeletionEventProcessingProc
Else
Application.Dialogs(xlDialogEditDelete).Show
End If
End Sub

Private Sub LineDeletionEventProcessingProc()
MsgBox "Rows ware deleted"
Selection.Delete xlUp
End Sub
</pre>