Excel VBA Delete the Entire Row if Value Matches the Cell in Another Sheet

[Chang123]

Hey there, I have a workbook that contains two works sheets, let's say Sheet1 and Sheet2.

What I want is that for the cells in Sheet2 Column A, if the cell value equals to the Sheet1 Cell D5, then delete the entire row.

Can you help to let me know how to make it with VBA? Thanks!

 

[johnnyL]

How about:

Sub DeleteRowIfValue()
'
    Dim LastRowInRange  As Long, RowCounter As Long
'
    LastRowInRange = Sheets("Sheet2").Range("A:A").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Returns a Row Number
'
    For RowCounter = LastRowInRange To 1 Step -1                                            ' Count Backwards
        If Sheets("Sheet2").Range("A" & RowCounter) = Sheets("Sheet1").Range("D5") Then     '   If Cell matches our 'Delete if' value then
            Sheets("Sheet2").Rows(RowCounter).EntireRow.Delete                              '       Delete the row
        End If
    Next
End Sub

 

[Chang123]

How about:

Sub DeleteRowIfValue()
'
    Dim LastRowInRange  As Long, RowCounter As Long
'
    LastRowInRange = Sheets("Sheet2").Range("A:A").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row            ' Returns a Row Number
'
    For RowCounter = LastRowInRange To 1 Step -1                                            ' Count Backwards
        If Sheets("Sheet2").Range("A" & RowCounter) = Sheets("Sheet1").Range("D5") Then     '   If Cell matches our 'Delete if' value then
            Sheets("Sheet2").Rows(RowCounter).EntireRow.Delete                              '       Delete the row
        End If
    Next
End Sub

Thank you! I tried this code. While the issue is that when I run the code, the Excel just had no reaction (the row didn't delete, and there's also no error warning). I have never met this kind of situation before, so I don't know where's the issue in this case.

 

[johnnyL]

I verified the code works. Did you change the sheet names in the code to reflect your sheet names being used?

Sheet1 that has the value to search for in D5
Sheet2 The values in column A that are searched through

 

[Chang123]

I verified the code works. Did you change the sheet names in the code to reflect your sheet names being used?

Sheet1 that has the value to search for in D5
Sheet2 The values in column A that are searched through

Yes, I did, and that's weird.

 

[Chang123]

I verified the code works. Did you change the sheet names in the code to reflect your sheet names being used?

Sheet1 that has the value to search for in D5
Sheet2 The values in column A that are searched through

I found the reason. It was because the format of Sheet1 D5 was Text. After I changed it to General, the issue was fixed. Thanks!

 

[sanjeewa2004]

Hi, I need to find D:D and delete in excel sheet2. not only D5 value. please help me. thank you