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

Martha Mary

New Member
Joined
May 5, 2022
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,142
Office Version
  1. 2016
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

New Member
Joined
May 5, 2022
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
May 5, 2022
Messages
17
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to MrExcel.
How about
VBA Code:
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:

1651757887379.png


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
Did you get any error messages when you ran the macro?
 

Martha Mary

New Member
Joined
May 5, 2022
Messages
17
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Did you get any error messages when you ran the macro?
No error message - just that nothing happened
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
In that case check that the columns A & M don't have any trailing spaces.
 

Martha Mary

New Member
Joined
May 5, 2022
Messages
17
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,297
Office Version
  1. 365
Platform
  1. Windows
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,410
Members
430,425
Latest member
xlsee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top