Delete the entire row in another worksheet

Question

In Sheet1 there is one number in A3 (which is not always the same)

enter image description here

In Sheet2, I want to delete the entire row where column A contains the same value as Sheet1!A3 (in blue):

enter image description here

With a different number:

enter image description here

As before, we delete the row:

enter image description here

...and so on.

Here is my code, but nothing happened; no error, but no rows deleted.

Dim rng As Range
    Dim WB As Workbook
    Dim i As Integer

      Application.ScreenUpdating = False
      Set WB = ActiveWorkbook
      Set rng = WB.Sheets("Données Enregistrées").Range("B:B").Find(What:=WB.Sheets("Modification").Range("A3"), _
             LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
      While Not rng Is Nothing
        rng.Rows("Modification").EntireRow.Delete
        Set rng = WB.Sheets("Données Enregistrées").Range("B:B").Find(What:=WB.Sheets("Modification").Range("A3"), _
                  LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
      Wend

      Application.ScreenUpdating = True

You probably need to set the code in the OnChange part. Have a look at [this topic](http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) — CustomX, Mar 18 '15 at 14:05
You appear to have asked basically the same question six times in the last day. **Please stop**. — jonrsharpe, Mar 18 '15 at 14:09
I *didn't answer*, I commented. Whether or not I can (or will!) help is irrelevant; you are **not going about asking correctly**. Please see http://stackoverflow.com/help/how-to-ask and for the love of everything stop posting images of previous questions. If you carry on like this you will likely end up with a [question ban](http://stackoverflow.com/help/question-bans). — jonrsharpe, Mar 18 '15 at 14:26

score -1 · Accepted Answer · answered Mar 18 '15 at 14:37

-1

Ok man this code will do the job for you i think at least on the delete part, but you must adapt to the rest of your code

You wasen't clear about how many time it happens so just build a loop if you need

Dim f       As String

f = Worksheets(1).Range("A3")
'Since i didn't got that clear, here above you must create 
'a code to declare "f" as whatever you want the code will than
'search that f value on the "A" column of Sheets(2) and delete 
'the first that contains that value, build a loop if it happens 
'more than once

Set c = Worksheets(2).Range("A:A").Find(f)

Range(c.address()).EntireRow.Delete

answered Mar 18 '15 at 14:37

Ygor Yansz

176
1
4
12

Dear, Many Thanks))) It has to delete just one row once, so I don't think that I need the loop)))Thanks I'll try.. – Affolter Mar 18 '15 at 14:55
Dim f As String f = Worksheets("Modification").Range("A3") 'Since i didn't got that clear, here above you must create a code to declare "f" as whatever you want Set c = Worksheets("Données Enregistrées").Range("A1:A20").Find(f) Range(c.Address()).EntireRow.Delete – Affolter Mar 18 '15 at 15:40
I have tried with this code, but very strange it has deleted one row but not the good one. It has deleted the Row in A3 (Sheet1=Modification), and not the row in the sheet 2 = Données Enregistrées. – Affolter Mar 18 '15 at 15:42
Place a worksheets(2). before the range(c.address()) – Ygor Yansz Mar 18 '15 at 16:14
Great!!! Many Thanks;) You are so kind to help me;))Can I ask you something more?? :/ In my exemple, if I put 5(in A3), 5 is not present in the column 1 of the sheet2, and I have an Error (Debog). – Affolter Mar 18 '15 at 16:38
As you have seen, Im really bad:/ I think that I need to make a IF"A3 Value is present in coumn1 Sheet2" Then " (All of Code that you have done) " Else "Nothing" <----- How can I write that?? Is it the good Idea? Thanks a lot – Affolter Mar 18 '15 at 16:41
If Worksheets("Modification").Range("A3") = Worksheets("Données Enregistrées").Range("A:A") Then Dim f As String f = Worksheets("Modification").Range("A3") 'Since i didn't got that clear, here above you must create a code to declare "f" as whatever you want Set c = Worksheets("Données Enregistrées").Range("A:A").Find(f) Worksheets("Données Enregistrées").Range(c.Address()).EntireRow.Delete End If – Affolter Mar 18 '15 at 16:52
But still an error:/ – Affolter Mar 18 '15 at 16:53

Delete the entire row in another worksheet

1 Answers1