Solved Delete row in Sheet 1 if cell matches in Sheet 2

April 2, 2019 at 11:19:51
Specs: Windows
Here is my scenario,

I have Sheet 1 as "Outstanding Orders" and Sheet 2 as "Closed Orders". In both sheets, each row contains different order data with Column B listing the order ID. I would like to have Excel automatically deleted a row from sheet 1 if I input the same order ID into column B sheet 2. In other words, if column B is the same in both sheets, delete the row in sheet 1. Note that the row will be different.

How would I best perform this?

message edited by Steven4321


See More: Delete row in Sheet 1 if cell matches in Sheet 2

Reply ↓  Report •

#1
April 3, 2019 at 07:01:01
✔ Best Answer
Try putting the following code in the sheet module for the Closed Order sheet.

I suggest that you try this in a test copy of your workbook since chances made by a macro can not be easily undone. In other words, the Undo button or Ctrl-Z will not replace any deleted data.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if a single cell in Column B was changed
   If Target.Column = 2 Then
    If Target.Cells.Count = 1 Then

'Search for entry on Outstanding Orders sheet
      With Sheets("Outstanding Orders").Columns(2)
        Set schId = .Find(Target, lookat:=xlWhole)

'Delete Row if found
          If Not schId Is Nothing Then
            schId.EntireRow.Delete
          End If
      End With
    End If
   End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
April 9, 2019 at 06:32:44
I have a follow up question regarding the same sheet.

I've been playing around with pulling data from my "master" order list into another excel file. The goal here is to have a monthly order list that I can protect and send to multiple recipients for viewing without having to send them the "master".

I currently have it working by pulling information from row 1 through row 31 of my "Closed Orders". My master is in descending order, so this pulls the header and the last 30 orders into the new sheet. My issue is that this does not include any pending orders from my "Outstanding Orders" sheet.

Goal:
Pull the last 30 days from both the "Outstanding Orders" sheet and the "Closed Orders" sheet. Combine both lists in date order into the new document, preferably in descending order. Column A on both sheets is the order date. Information to pull is populated in column A through D only. Note that more columns are used in the master, but I don't want to send out all of that information.

Can this be done? Can I lock the entire sheet after and still have the formulas/codes work as intended?

Thank you.

message edited by Steven4321


Reply ↓  Report •

#3
April 9, 2019 at 07:50:14
re: "I currently have it working by pulling information from row 1 through row 31 of my "Closed Orders"."

You currently have what working? The question in Response # 2 doesn't sound like it is related to the question in this thread. If you are asking a totally different question, even if it involves the same workbook, please start a new thread with a relevant subject line.

Make sure that you include any details that we may need in order to answer that specific question.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question