Deleting specific rows from multiple sheets

Closed
excelss - Sep 4, 2015 at 10:43 AM
JDkas Posts 1 Registration date Sunday March 14, 2021 Status Member Last seen March 15, 2021 - Mar 15, 2021 at 07:18 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.


2 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Sep 7, 2015 at 03:30 AM
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
JDkas Posts 1 Registration date Sunday March 14, 2021 Status Member Last seen March 15, 2021
Mar 15, 2021 at 07:18 AM
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
0