Deleting specific rows from multiple sheets

[Closed]
Report
-
Posts
1
Registration date
Sunday March 14, 2021
Status
Member
Last seen
March 15, 2021
-
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.


2 replies

Posts
1311
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 28, 2021
232
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.
2
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
1
Registration date
Sunday March 14, 2021
Status
Member
Last seen
March 15, 2021

Hello
Very interesting, but i have a problem
In my project the "ID" is diferent in each sheet.
how to solve?
Please Help me
Thank you
Posts
1311
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
July 28, 2021
232
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!