Deleting specific rows from multiple sheetsClosed

Question

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.


Configuration: Windows 8 / Firefox 40.0

vcoolio · Accepted Answer

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.

vcoolio · Answer

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.

Deleting specific rows from multiple sheets

2 responses

Similar discussions

Subscribe To Our Newsletter!