Delete rows of other sheets also

[sujittalukde]

Please refer to the post at the following link:
http://www.mrexcel.com/board2/viewto...79062&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.
[vba]
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

[/vba]

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

[vba]

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

[/vba]

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