+ Reply to Thread
Results 1 to 3 of 3

Event Macro upon deleting a row

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Event Macro upon deleting a row

    Hi,

    I am looking for an Event Macro to return the number of a deleted Row.

    I usually delete a row by right-clicking on the row number->delete.

    I don't know if it this will make it easier - but if so - I can tolerate with right-clicking a cell->Deletion...->EntireRow.

    All I need is the deleted(!) Row number.

    And here comes the reason:

    In one of my worksheets I have Part-Numbers in column "B" and a picture of each placed over a cell in the same row in Column "D".

    I found the following code in the web.

    The code works fine as long as I provide the row number (like 17 in the code below) but I do not want the code to be fired manually and I would like the Event MAcro to be able to find the row number to be deleted.
    I'd like it to fire upon each row deletion - with Worksheet_Change or Worksheet_SelectionChange but as far as I understand the code needs to know the deleted row number, in order to delete the picture as well.

    Thanks, Elm

    Option Explicit
    Sub TestMe()
    Dim shp As Shape
    Dim testStr As String
    Dim RowToDelete As Long
    Dim TopLeftCellRow As Long
    Dim OkToDelete As Boolean
    RowToDelete = 17
    ''' RowToDelete = Target.Row
    ''' MsgBox Target.Row
    For Each shp In ActiveSheet.Shapes
    TopLeftCellRow = 0
    On Error Resume Next
    TopLeftCellRow = shp.TopLeftCell.Row
    On Error GoTo 0
    If TopLeftCellRow = 0 Then
    'not a shape to delete, so skip it
    Else
    If TopLeftCellRow = RowToDelete Then
    OkToDelete = True
    If shp.Type = msoComment Then
    OkToDelete = False
    ElseIf shp.Type = msoFormControl Then
    If shp.FormControlType = xlDropDown Then
    'it might be a data|validation dropdown
    testStr = ""
    On Error Resume Next
    testStr = shp.TopLeftCell.Address
    On Error GoTo 0
    If testStr = "" Then
    OkToDelete = False
    End If
    End If
    End If
    If OkToDelete Then
    shp.Delete
    Cells(RowToDelete, 1).EntireRow.Delete
    End If
    End If
    End If
    Next
    End Sub
    Last edited by ElmerS; 06-28-2009 at 01:27 PM.

  2. #2
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Re: Event Macro upon deleting a row

    I managed to find some of a solution:
    (Change or check and define total rows/columns according to Excels version)

    Private Sub Worksheet_Change(ByVal Target As Range)
      Application.EnableEvents = False
      If Not Intersect(Target, Range("A1:A65536")) Is Nothing And _
         Not Intersect(Target, Range("IV1:IV65536")) Is Nothing Then
         If Target.Cells.Columns.Count Mod (256) = 0 Then
            MsgBox "Deleted Row Number: " & Target.Row
              ''' do what ever needed
         End If
      End If
      Application.EnableEvents = True
    End Sub
    so far so good - but when I try to combine the two codes is messes up.

    1) Every change/edit/typing - fires the Event-Macro instead of only an entire row deletion.
    2) Upon adding a line it fires too.
    3. Deleting row 7 works OK but upon deleting row 5 the macro deletes row 7 as well.

    Any Ideas ?
    It seems to be close to a smooth solution.

    Thanks, Elm
    Last edited by ElmerS; 06-28-2009 at 06:11 PM.

  3. #3
    Forum Contributor
    Join Date
    11-28-2008
    Location
    Reykjavík, Iceland
    MS-Off Ver
    Any of: 2003 & 2007
    Posts
    412

    Re: Event Macro upon deleting a row

    Sorry for all those posts but I could not find a way to add the WB upon editing my previous post.

    So here it is.

    Thanks, Elm
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1