Delete rows of other sheets also

S

sujittalukde

Well-known Member
Joined
Jun 2, 2007
Messages
520
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
This code I am using now for deleting rows for selected cell
Code: 
Sub DeleteRow()
    Dim a
    Dim intActiveRow As Integer
    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
        With Range("A" & intActiveRow)
            .EntireRow.Delete
        End With
    End If
     
    ActiveSheet.Protect Password:="abcd"
     
    Application.ScreenUpdating = True
End Sub

However this code has to run for all sheets separately I want that as soon the code is run in MasterData sheet, that record shall also be deleted from other related sheets.
 
Upvote 0
I'm assuming that the other sheets are linked to Master. If you are using VLOOKUP, it's quite easy to clean out mismatches in the other sheets because you will get an error with the VLOOKUP formula. So, code like...
Code: 
Range("A:A").SpecialCells(xlCellTypeFormulas,xlErrors).EntireRow.Delete

Should do the job. Is that what you were after?

Denis
 
Upvote 0
Thanks for the reply.

I have updated the code as below

Code: 
Sub DeleteRow()
    Dim a
    Dim intActiveRow As Integer
    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
        Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
     
    End If
      
    ActiveSheet.Protect Password:="abcd"
      
    Application.ScreenUpdating = True
End Sub

But it is showing an error. Also I am not using VLOOKUP as other sheets need calculation and also other fields are there in other sheets Only for names =masterdata!B5 is used.
 
Upvote 0
So, is there any connection between the Master sheet and the other sheets that would make it easy to find and delete the rows?

For example, is it always the same row as the one that was deleted in Master?
Are the other sheets all in a group (eg, Master is to the left or the right of ALL the sheets)?

Denis
 
Upvote 0
Thanks for the reply.
1. Yes there is aconnection between master data sheet and other sheets Other sheets are linked to masterdata sheet. It is the name of the persons of the master sheet in column B that is linked to column B of other sheets.

2. the row no is not same for the master data sheet and other sheets.

3. All the sheets are right to the master sheet but the last one is not related to master data sheet. The last sheet is independant.

THough i tried to clarify the facts in case you need more, do let me know.
 
Upvote 0
OK. If column B on the other sheets is linked to Master, and you delete a row in Master, you should get an error in Column B of the other sheet. That being the case, change this
Code: 
        Range("A:A").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
to this
Code: 
        Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete

Denis
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Fixing VBA Code
2
Replies
11
Views
741
helplessnoobatexcel
H
J
  • Question
How to delete row on two worksheets
Replies
2
Views
321
jdcar
J
B
  • Question
Getting error if linked cell row deleted in different sheet of large data.
Replies
3
Views
127
Peter_SSs
Peter_SSs
C
  • Solved
Recording a Macro that Copies & Pastes into Other Sheets
Replies
1
Views
302
AlanY
A
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
147
SunnyAlv
SunnyAlv

Forum statistics

Threads
1,215,219
Messages
6,123,681
Members
449,116
Latest member
HypnoFant

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top