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?

 

[sujittalukde]

to get the solution and to carry forward from here I have also posted the same at vbaexpress. Lets hope it will be solved there. I am looking here also for getting any solution.
http://vbaexpress.com/forum/showthread.php?p=104688#post104688

 

[SydneyGeek]

I think this is a case where you have asked for something that Excel doesn't usually handle. Did you give Parry's code a try, to see if it indicated a deletion?
If it does work, you could then call the code in the standard module to run the clean-up.

However, this is something that Access does right out of the box. If you delete a record, you can also delete related records automatically.

Denis

 

[sujittalukde]

Hi Denis, Thanks for reply. I have tried Parry's code It is showing a message of row deletion on masterdata but not deleting the rows of other sheets.

Can this situation be handled in some other ways say with the help of a temporary column etc or any othr method which will tell the code to delete the rows? this I am guessing as I self dont know whether this kind of thing is possible or not.

 

[tusharm]

(A) Use a database program that enforces deletes from the "bottom up" so to say so that the master record is deleted last. MS Access calls it "referential integrity."

(B) Even better, don't delete records. Instead add a column 'Inactive' or better yet 'Inactive Date' and set it appropriately when the person leaves or even better link it to another table that has 'start' and 'end' dates. Now, you can make the necessary inferences about employment status based on available start and end dates.

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.

 

[mdmackillop]

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

Hi Denis,
You seem to be missing the line to delete the original row which creates the other errors for subsequent deletion.

a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
Activecell.EntireRow.Delete
    If a = vbYes Then

Is the error always going to be in Column B? If not, use Cells instead of Range("B:B")
Regards
MD

 

[SydneyGeek]

Thanks MD, I didn't have the deletion step in there because this code is meant to respond *after* the original deletion, and just fo the clean-up on the other sheets.

Tushar makes some good points that you should consider. However, if you are determined to delete the rows, try this (adapting Parry's code and my suggestion):

In the worksheet module (View > Code)

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
        Call DeleteRow
    End If
End If

End Sub

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

In a standard module:

Public UsedRangeCount As Long

Sub DeleteRow()
    Dim a
    Dim intActiveRow As Integer
    Dim Sht As Worksheet
    
    Application.ScreenUpdating = False
    intActiveRow = ActiveCell.Row
    ActiveSheet.Unprotect Password:="abcd"
    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
      
    Application.ScreenUpdating = True
End Sub

Denis

 

[sujittalukde]

Hi Md, Thanks for sorting out problem

Hello Dennis, your last is working perfectly. Finally you reached the target. It seems "Thanks" would a smaller word than your effort & help but still I think, it is the beautiful word to recognize your help

Thanks to you and to all who participated in this post and helped us in sorting out the problem.