Excel VBA Check value in one cell from Sheet1 and delete entire rows if matches in Sheet2

[booton]

Hello everyone!

I have 2 sheets. In sheet1 cell C2 i have a dropdown list. After selecting something there i need a macro to check if the text in C2 sheet1 is matching the information in sheet2 column A and if it does to delete every row that has that information. Can someone please help me?

Thank you!!!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in C2 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Range("A1").CurrentRegion.AutoFilter 1, Target
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

The macro assumes your have headers in row 1 of Sheet2 and that the data starts in row 2 with no blank rows.

 

[booton]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in C2 and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Range("A1").CurrentRegion.AutoFilter 1, Target
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub

The macro assumes your have headers in row 1 of Sheet2 and that the data starts in row 2 with no blank rows.

What have you done it is perfect. thank you so much. the functionality it is exactly what i need. but i have one more thing to be perfect.

I already have this macro on in the file that is connect to a button.

Sub Copy_Range()
Dim i As Integer
    i = Range("A500000").End(xlUp).Row
Range("A2:P" & i).Copy    
    Sheets("Repository").Select
        Range("A500000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues    

Sheets("Repository").Visible = True
Sheets("Calculation").Select
End Sub

Sheet2 is sheet Repository. Sheet1 it is sheet Calculation. i already have a macro that copy a range of data intro sheet2 (sheet repository) but before that i need to check if c2 text from sheet1 (calculation) is present in column A2 in sheet 2 (repository)

Thank you so much for all your help! let me know if i was clear enough

 

[mumps]

Are you saying that you want to combine the two macros if c2 text from sheet1 (calculation) is present in column A in sheet 2 (repository)?

 

[booton]

Are you saying that you want to combine the two macros if c2 text from sheet1 (calculation) is present in column A in sheet 2 (repository)?

Yes. so the idea is to check if in sheet2 repository is there any data in column A that match c2 cell in sheet1 calculation. After the check is done, to continue with the macro that i sent you. that copy paste from a range.

Thank you so much!

 

[mumps]

DO you want to copy/paste the range before you delete the rows that match or do you want to delete the rows first and then copy/paste the range?

 

[booton]

DO you want to copy/paste the range before you delete the rows that match or do you want to delete the rows first and then copy/paste the range?

i want to copy paste the range after

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Repository")
        .Range("A1").CurrentRegion.AutoFilter 1, Target
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        Range("A2", Range("P" & Rows.Count).End(xlUp)).Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Visible = True
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[booton]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "C2" Then Exit Sub
    Application.ScreenUpdating = False
    With Sheets("Repository")
        .Range("A1").CurrentRegion.AutoFilter 1, Target
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        Range("A2", Range("P" & Rows.Count).End(xlUp)).Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        .Visible = True
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Hello again. Unfortunately i did not make myself clear i did not explained good what i need.

I already have this macro

Sub Copy_Range()

Sheets("Input_to_ResQ").Visible = True
Sheets("Repository").Visible = True
Sheets("Input_to_ResQ").Select

Dim i As Integer

    i = Range("A500000").End(xlUp).Row
'    MsgBox i

Range("A2:P" & i).Copy
    
    Sheets("Repository").Select
        Range("A500000").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        
Sheets("Repository").Visible = True
Sheets("Input_to_ResQ").Visible = False
        
Sheet19.Select

End Sub

This macro does exactly what i need in terms of the copy/paste range. A button is linked to this macro and when i click it, it does exactly what i need.

now what i need is what you did to be included in this macro as a first step. when i click that button. to check if the data in c2 sheet1 is present in sheet2 column A. if yes delete every row. after that to continue with the macro from above because i do not need to change anything in it. <3

Thank you again!!!

 

[mumps]

Delete the previous macro from the worksheet code module and replace your current macro with this one:

Sub Copy_Range()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet
    Set srcWS = Sheets("Input_to_ResQ")
    srcWS.Visible = True
    Sheets("Repository").Visible = True
    With Sheets("Repository")
        .Range("A1").CurrentRegion.AutoFilter 1, srcWS.Range("C2").Value
        .AutoFilter.Range.Offset(1).EntireRow.Delete
        .Range("A1").AutoFilter
        srcWS.Range("A2", srcWS.Range("P" & Rows.Count).End(xlUp)).Copy
        .Cells(.Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
    End With
    srcWS.Visible = False
    Sheet19.Select
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub