Need to edge out the competition for your dream job? Train for certifications today.
' 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
' Turn on screen updating
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Open in new window
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.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.