Deleting specific rows from multiple sheets [Closed]

- - Latest reply: vcoolio
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
- Sep 7, 2015 at 03:30 AM
Hello,

I have a question on how to do something specific in excel. I have a workbook with 3 sheets. Sheet 1 contains a master list of records. Sheet 2 and 3 are subsets of Sheet 1. There is a unique record identifier on all sheets in column A. So, if I delete a row in Sheet 1, how do I get it to automatically delete the same record in sheet 2 and/or 3 if it exists there.

Thanks.


See more 

2 replies

Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
201
0
Thank you
Hello Excelss,

Try the following code in a standard module:-

Sub DeleteRowsAllSheets()

   Dim MyArr As Variant
   Dim x As Long
   Dim a As Long
   
MyArr = Array("Sheet1", "Sheet2", "Sheet3")
a = Selection.Row

For x = LBound(MyArr) To UBound(MyArr)
    Sheets(MyArr(x)).Rows(a).EntireRow.Delete
Next x

End Sub


Following is a link to my test work book for you to peruse:--

https://www.dropbox.com/s/y6ykwvkm4bqoxoy/Excelss.xlsm?dl=0

Please note that data in all rows in all sheets has to be in the same position for the code to work properly.
Select an ID number in Column A of Sheet 1, click on the Delete Rows button and the same row will be deleted from all sheets.

In the MyArr array, you can increase/decrease the number of sheets to suit your needs.

I hope that this helps.

Cheerio,
vcoolio.
Posts
1241
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 13, 2019
201
0
Thank you
Hello again Excelss,

I was just thinking that perhaps your ID in Column A of each sheet may not necessarily be in any uniform order. For example, in Sheet 1 an ID may be in row 4, in Sheet 2 the same ID may be in row 8 and in Sheet 3 the same ID may be in row 15.

So to cover this possibility, the following code may be a better option:-


Sub DeleteData()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim lRow As Long
Dim IDRef As String
IDRef = InputBox("Please enter the selected ID.")
If IDRef = vbNullString Then Exit Sub


For Each ws In Worksheets
    ws.Select
    
lRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:A" & lRow)
        If cell = IDRef Then
        cell.EntireRow.Delete
        End If
Next cell
Next ws

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheet1.Select

End Sub


This code will find the same ID in each sheet and delete it from each sheet.

Following is a link to my updated test work book:-

https://www.dropbox.com/s/9ivpm9g330veykj/Excelss%282%29.xlsm?dl=0

Click on the button and an Input Box will appear asking you to type in an ID. Click OK and you're done.

I hope that this helps.

Cheerio,
vcoolio.