Deleting row in another sheet depending on the value of a cell in the first sheet

[Martha Mary]

Hi
I have some VBA code which works fine to delete all rows in Sheet2 if the corresponding ID exists in Sheet1:
Sub CheckA()
Dim LR As Long, i As Long
With Sheets("Sheet1")
LR = .Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
If IsError(Application.Match(.Range("A" & i).Value, Sheets("Sheet2").Columns("A"), 0)) Then Rows(i).Delete
Next i
End With
End Sub

However I only want to delete the corresponding rows in Sheet2 if the contents of column M in Sheet1= "No" - can anyone advise how I need to amend this code to do this?

Appreciate any advice!

 

[bebo021999]

1) You said delete rows in sheet2, but your code said delete rows in sheet1. Which is correct?
2) for the new requirement, if "No" appears in sheet1, column M then delete what rows in sheet2?

 

[Martha Mary]

Basically i have a list of records with unique IDs in Sheet1, and some of these records appear in Sheet2 with the corresponding unique ID. I want to delete the rows in Sheet2 where the the cells in the M column in Sheet1 = "No"

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub MarthaMary()
   Dim Cl As Range, Rng As Range
   Dim Dic As Object
   
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If LCase(Cl.Offset(, 12).Value) = "no" Then Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("Sheet2")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

 

[Martha Mary]

Hi & welcome to MrExcel.
How about

Sub MarthaMary()
   Dim Cl As Range, Rng As Range
   Dim Dic As Object
  
   Set Dic = CreateObject("scripting.dictionary")
   With Sheets("Sheet1")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If LCase(Cl.Offset(, 12).Value) = "no" Then Dic(Cl.Value) = Empty
      Next Cl
   End With
   With Sheets("Sheet2")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         If Dic.exists(Cl.Value) Then
            If Rng Is Nothing Then Set Rng = Cl Else Set Rng = Union(Rng, Cl)
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then Rng.EntireRow.Delete
End Sub

Hi Fluff thanks so much for your reply.

Unfortunately the rows are still there in Sheet 2 as shown below:

The corresponding rows with the unique IDs 010002 and 010005 in Sheet 1 have now had their column M updated to 'No' so what I need now is for the rows in the example above to disappear in Sheet2. Does this make sense?

 

[Fluff]

Did you get any error messages when you ran the macro?

 

[Martha Mary]

Did you get any error messages when you ran the macro?

No error message - just that nothing happened

 

[Fluff]

In that case check that the columns A & M don't have any trailing spaces.

 

[Martha Mary]

In that case check that the columns A & M don't have any trailing spaces.

I don't think so......

this is the code I use to update Sheet2 from Sheet1:

Sub CopyRow()
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Dim x As Long
x = 2
Dim Rng As Range
For Each Rng In Sheets("Sheet1").Range("M2:M" & LastRow)
If Rng = "Yes" Then
Rng.EntireRow.Copy
Sheets("Sheet2").Cells(x, 1).PasteSpecial xlPasteValues
x = x + 1
End If
Next Rng
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

This works perfectly - all I want to try to do now is get this in reverse? If someone updates the M column in Sheet1 to 'No' again, then I want that row removed from Sheet2

 

[Fluff]

If there are no leading/trailing spaces in the data, then I'm not sure why it isn't working for you.
Can you provide your workbook?