Compare two sets of columns for equal values

[utkarshdesai]

I'm still a beginner with VBA and i'm trying to make this macro in excel.


So i have a column in a sheet2 where string is stored(acts as a db) and added periodically upon need and i get a set of data everyday which i need to compare to the db and whichever doesnt match stays and the once matched should be deleted from the current sheet. i hide the db sheet so i have to compare data by keeping in in sheet1 for example.


With my research over the internet i figured to write this small macro to fulfill my task but somehow it works sometimes and it doesnt work sometimes.


Sub Reporting()


Dim x As Long

Dim LastRow As Long


Dim q As Variant


Dim rsht1 As Long

Dim rsht2 As Long

Dim i As Integer

Dim n As Integer

Dim j As Integer


Sheets(1).Activate

Application.ScreenUpdating = False


LastRow = Range("A65000").End(xlUp).Row

For x = LastRow To 1 Step -1

If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then

Range("A" & x).EntireRow.Delete

End If

Next x


Sheets(1).Activate


Do While n < 16


n = n + 1


rsht1 = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row

rsht2 = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row


For i = 1 To rsht1

For j = 1 To rsht2

If Sheets(1).Range("B" & i) = Sheets(2).Range("C" & j) Then

Sheets(1).Rows(i).Select

Selection.Delete Shift:=xlUp

End If

Next

Next


Loop


Application.ScreenUpdating = True

MsgBox "Done"


End Sub