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

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...)

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

[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

[Tikas A. Planck]

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.

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

[Colo]

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

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