if sheet2 data is present in sheet1 then delete that complete row

if sheet2 data is present in sheet1 then delete that complete row
this highlighted colour is only for understanding
i have to do this by vba
Book3.xlsm
Avinash SinghAsked:
Who is Participating?
 
Bill PrewCommented:
Here is a small VBA procedure that will do what you wanted for that data set.

Sub DeleteMatchingRows()
    ' Define local variable
    Dim shtMain As Worksheet
    Dim shtData As Worksheet
    Dim rngMain As Range
    Dim rngData As Range
    Dim lngRow As Long

    ' Turn off screen updating
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    ' Work with Sheet1 and Sheet2
    Set shtMain = ThisWorkbook.Worksheets(1)
    Set shtData = ThisWorkbook.Worksheets(2)
    
    ' Key values found in first column, starting at first row
    Set rngMain = shtMain.Range(shtMain.Cells(1, 1), shtMain.Cells(shtMain.Cells(shtMain.Rows.Count, 1).End(xlUp).Row, 1))
    Set rngData = shtData.Range(shtData.Cells(1, 1), shtData.Cells(shtData.Cells(shtData.Rows.Count, 1).End(xlUp).Row, 1))

    ' Check value from first sheet against second, delete row if match found
    For lngRow = rngMain.Rows.Count To 1 Step -1
        If Not IsError(Application.VLookup(rngMain.Cells(lngRow, 1), rngData, 1, False)) Then
            rngMain.Rows(lngRow).EntireRow.Delete
        End If
    Next

    ' Turn on screen updating
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window


»bp
0
 
Avinash SinghAuthor Commented:
Thnx Bill Prew Sir for giving ur precious time to this post
0
 
Bill PrewCommented:
Welcome.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.