Delete rows of other sheets also

[sujittalukde]

Please refer to the post at the following link:
http://www.mrexcel.com/board2/viewtopic.php?t=279062&start=10
Little background:
If a record is deleted from "Master data "Sheet the same record should also get deleted from other sheets which is dependant on master data sheet These dependent sheets are linked to masterdata sheet vide =masterdata!B45 (for eg.).

At that forum some esteemed members have tried to solve the same and succeed to a large extent. the code that has been provided there have identified the dependant rows to be deleted but those codes are not deleting the rows of other sheet automatically on deletion of the record from masterdata sheet.

So my requirement is:

When the user will delete the row from the masterdata sheet the code will delete the record from master data sheet and the code will automatically delete the record from other sheets also.

For ready reference I am quoting the code provided at that forum by Denis:


Sub DeleteRow()
Dim a
Dim intActiveRow As Integer
Dim Sht As Worksheet

Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
If a = vbYes Then
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> "MasterData" Then
Sht.Activate
Sht.Unprotect Password:="abcd"
Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Sht.Protect Password:="abcd"
End If
Next Sht
End If

Application.ScreenUpdating = True
End Sub


This I am posting here to get a solution if some one at this forum can solve it.

In case more clarification is required, please get in touch. I'll feel happy to offer clarification.

[mdmackillop]

Note; If the link can occur more than once on any row, some checking is required to avoid adding the address to the array a second time.

Option Explicit
Sub DeleteRow()
Dim a
Dim intActiveRow As Integer
Dim Sht As Worksheet
Dim arr()
Dim i As Long
Dim j As Long
Dim DelAdd As String
Dim c As Range
Dim FirstAddress As String
ReDim arr(1, 100)
Application.ScreenUpdating = False
i = -1
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
If a = vbYes Then
DelAdd = ActiveCell.Address(0, 0)
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> "MasterData" Then
With Sht.Cells
Set c = .Find("=MasterData!" & DelAdd, LookIn:=xlFormulas, searchorder:=xlRows)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
i = i + 1
arr(0, i) = Sht.Name
arr(1, i) = c.Address
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End If
Next
End If
ReDim Preserve arr(1, i)
For j = i To 0 Step -1
With Sheets(arr(0, j))
.Activate
.Unprotect Password:="abcd"
.Range(arr(1, j)).EntireRow.Delete
.Protect Password:="abcd"
End With
Next
Sheets("MasterData").Range(DelAdd).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

[mdmackillop]

... or amend Denis's code as follows. I'm a bit wary of this in case other rows are deleted due to errors not resulting from the Row deletion.



Sub DeleteRow()


Dim a

Dim intActiveRow As Integer
Dim Sht As Worksheet


Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
If a = vbYes Then
ActiveCell.EntireRow.Delete
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> "MasterData" Then
Sht.Activate
Sht.Unprotect Password:="abcd"
Cells.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Sht.Protect Password:="abcd"
End If
Next Sht
End If


Application.ScreenUpdating = True
End Sub

[sujittalukde]

Thanks MD Dennis has provided the code which is working perfectly. In your codes above, the problem of non deleting the rows on other sheets is still there. Nevertheless, thanks for the effort and help for sorting out the matter.