Delete rows of other sheets also

[sujittalukde]

I have a WB where the first sheet is "Introduction", second is "Master Data" wherein the name of players with some details are kept.

After that some sheets are there which are linked to "Master Data" ie say Sheet2,sheet3,Sheet5 (sheet 4 is not linked to "Master data" but appears after sheet3)

I want that if a record , say record 3 at row 5 is deleted also delete that record 3 in other linked sheets ie Sheet2,sheet3,Sheet5 The positions of the records differ from sheet to sheet ie if record 3 is at row 5 of master sheet it coulsd be on row 8 of sheet2, at row 10 of sheet3 & at row 6 of sheet 5. All the name of the person are linked to sheets with =masterdata!B5 like this.

Can a macro to this be provided?

 

[SydneyGeek]

OK. Is the Master linked to the other sheets, or are they linked to the Master?

Where is the link?

Denis

 

[sujittalukde]

Whwn a person joins in his/her name is entered in the masterdata sheet in column B. then on next sheet say sheet 1 in column B, =masterdata!cellRef is done. Similarly, when a person leaves, his name is deleted from the master data sheet & then from the other sheets as deleting the rows from the masterdata sheet gives an error on next sheets. This I am doing manually.So other sheets are linked to masterdata sheet. The links are on other sheets. On master data sheets, data are entered via keyboard.

I tried to clear your doubts, but as in this forum there is no option to attach a Wb, so still if you need some more clarification do let me know I'll be glad to offer the same.

 

[SydneyGeek]

OK. Once you delete the row in Master, go and check one of the other sheets.
You *should* have a #REF! error in the row that referred to the deleted cell. That's what I was talking about, so you need to

1. Delete the row
2. Go to each sheet in turn AFTER the deletion, and clean out any rows containing a REF error.

Does that help?

Denis

 

[sujittalukde]

Your code is working now & no error is coming but this is actually equal to my manual sysytem of running my original deletion code on each sheet. What I want is that the code should execute and delete all those rows which I am deleting manually after visiting each sheet automatically after deleting the record from the master sheet.

 

[SydneyGeek]

Try this:

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

Denis

 

[sujittalukde]

Sorry, but the code is not deleting rows in other sheets automatically on deleting the row from masterdata sheet.

Actually what my requirement is that -

I will delete the row from the masterdata sheet by this code itselt As soon as the code will delete a record from master data sheet it will automatically delete the row from other sheets also.

OR

It may also be done that if I delete a record from master data sheet with other or say by my original code, this code will excute and delete the rows of other sheets

 

[SydneyGeek]

Sorry, I can't find a way to do that. I will have a look, but what you require is a worksheet event that responds to cell deletion. I don't know if there is such a thing.

I will see if some other people can help

Denis

 

[sujittalukde]

Thanks for the reply & lets see other may solve it.

 

[parry]

Interesting problem. I dont believe there is a specific delete event to capture when a row was deleted. However, when you delete a row the ChangeEvent is fired. The issue is how do you know whether this is just a change in a value versus a whole row deleted.

Ive thought of a possible way but I would welcome people to challenge the logic as Im not entirely convinced that theres not some flaws with this approach (nagging doubt as it were) plus I havent got time at the moment so I only did a rudimentary test.

Logic...
1) Determine how many cells that contained data were in the sheet prior to the change.
2) Determine how many cells now have data after the change
3) If #1 is > #2 then the cells have either had their contents cleared or the cells deleted
4) If the number of cells that were changed is divisible by possible #cols in a row then the whole row must have been deleted.

#1 is achived by using a public variable and the selectionchange event while the remainder is achived in the change event...

in a standard module (eg Module1)

Public UsedRangeCount As Long

in the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurUsedRangeCount As Long

'the current number of cells with data
CurUsedRangeCount = ActiveSheet.UsedRange.Cells.Count


If UsedRangeCount > CurUsedRangeCount Then
    'If current rows with data is greater than the previous number then somethings either
    'been deleted or had its contents removed
    If Target.Cells.Count Mod Rows(1).Cells.Count = 0 Then
        'if the number of cells in the Target is divisible by the number of
        'columns that may be in a row then the whole row/s must have been deleted
        MsgBox "Rows " & Target.Address & " were deleted"
    End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UsedRangeCount = ActiveSheet.UsedRange.Cells.Count
End Sub

regards,
Graham

 

[sujittalukde]

Actually I self never thought that this is so much a big issue. anyway, thanks for the try and the effort you put in to solve the problem . But the code is also not deleting the rows of other sheets automatically. #REF is coming to other sheets but those rows are not deleting auto.