Deleting row depending on the content of a cell

[Oyens]

Hi,

I tried to create a macro to help me deleting row depending on the content of a cell.

I have one workbook containing two sheets. In Sheet A, you select several fields by using a drop down menu. Depending of you selecting on cell D6 and D7, Sheet B will delete some rows.
For example:
If sheet A cell D6= "firm" instead of "human" then
in sheet B you look in colomn A for "birth" and delete the entire row then
in sheet B you look in colomn A for "married" and delete the entire row then go back to sheet A
if sheet A in cell D7 = "FIP" then
in sheet B you look in colomn A for "Qrap" and delete the entire row

but if sheet A in cell D6 = "human" instead of "firm" then
in sheet B you look in colomn A for "kvk" and delete the entire row then
in sheet B you look in colomn A for "unit" and delete the entire row then go back to sheet A.

Many thanks in advance.

BR, Oyens

 

[Desu Nota from Columbus]

Sub test()

Dim r As Long
Dim lrow as Long
Application.ScreenUpdating = False

lrow = Worksheets("Sheet B").Range("A" & Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1

        If Worksheets("Sheet A").Range("D6").Value = "Firm" and Worksheets("Sheet B").Range("A" & i).Value = "birth" Then
            Worksheets("Sheet B").Rows(i).Delete
       ElseIf Worksheets("Sheet A").Range("D6").Value = "Firm" and Worksheets("Sheet B").Range("A" & i).Value = "married" Then
            Worksheets("Sheet B").Rows(i).Delete
      ElseIf Worksheets("Sheet A").Range("D6").Value = "Human" and Worksheets("Sheet B").Range("A" & i).Value = "kvk" Then
            Worksheets("Sheet B").Rows(i).Delete
       ElseIf Worksheets("Sheet A").Range("D6").Value = "Human" and  Worksheets("Sheet B").Range("A" & i).Value = "unit" Then
             Worksheets("Sheet B").Rows(i).Delete
      End If

    Next i

lrow = Worksheets("Sheet B").Range("A" & Rows.Count).End(xlUp).Row
    For i = lrow To 2 Step -1
      If Worksheets("Sheet A").Range("D7").Value = "Fip" and Worksheets("Sheet B").Range("A" & i).Value = "Qrap" Then
            Worksheets("Sheet B").Rows(i).Delete
      End If

Next i

Application.ScreenUpdating = True

End Sub

Try this

 

[Oyens]

Thank you so much.