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
Bookmarks