Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Macro that runs when entire rows are deleted?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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



    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    107
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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


    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  6. #6
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •