[Solved] delete row if the value not match

[bigme]

dear friends,
i have a workbook with 2 sheets.
in sheet1 in column A contains values
in sheet2 in column A contains values
i want to delete every rows in sheet2 if the value not match with value in sheet1
thank you.

regards,
bigMe

 

[Yousuf Shovon]

dear friends,
i have a workbook with 2 sheets.
in sheet1 in column A contains values
in sheet2 in column A contains values
i want to delete every rows in sheet2 if the value not match with value in sheet1
thank you.

regards,
bigMe

Hello BIGME,

Glad to hear from you again. I understand you wish to delete every row in sheet2 if the value does not match in sheet1. Fortunately, you can easily do this using the following VBA code:

Sub DeleteRowsNotInSheet1()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long
    Dim matchFound As Boolean
  
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
  
    lastRow1 = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
  
    For i = lastRow2 To 1 Step -1
        matchFound = False
        For j = 1 To lastRow1
            If ws2.Cells(i, 1).Value = ws1.Cells(j, 1).Value Then
                matchFound = True
                Exit For
            End If
        Next j
        If Not matchFound Then ws2.Rows(i).Delete
    Next i
End Sub

The code then enters a nested loop to check for matches between the values in Sheet1 and Sheet2. The outer loop starts from the last row in Sheet2 and loops back to the second row. Overall, this code provides a way to automatically delete rows in Sheet2 that do not match the values in Sheet1. Go to https://www.exceldemy.com/how-to-delete-every-other-column-in-excel/, if you wish to delete every other column in Excel simultaneously.

I have attached the Excel file for you to practice. Let me know if you have further issues with this.

Regards,
Yousuf Shovon

 

[bigme]

dear Yousuf,
I have try your code, and it's work very excellent.....
thank you for your help....

best regards,
bigMe

 

[Yousuf Shovon]

dear Yousuf,
I have try your code, and it's work very excellent.....
thank you for your help....

best regards,
bigMe

Dear BIGME,

We are glad the code was helpful to you. Thank you.