So I have 2 sheets: "Sheet1" and "Sheet2". Sheet2 has different values in range(D2: D6). The sheet1 range(A2:A6) is empty. Every time when I insert data in sheet1 range(A2:A6) it has to check if that data matches the data from sheet2 range(D2: D6). If the data matches in a cell, then it is kept in range(A2:A6), if not it is deleted from range(A2:A6).
Here is the code I made and which works:
I have attached also the excel file so you guys can better check and see the meaning of it.
My problem is that this code works only when I am pressing the bottom "press" which is in the Excel file. Instead I want my code to be smarter and avoid pressing the bottom. What I mean is that every time when an entry is entered in sheet1 range(A2:A6) the code should work automatically and delete the entries which don't match from sheet2 range(d2:d6). I want to delete the bottom and let the code to do its job automatically every time a data is inserted in range(A2:A6). To keep the data that match, and delete the ones which don't.
I am new here so hope I made myself clear.
Here is the code I made and which works:
Code:
Sub check()
Dim i As Integer, j As Integer
Dim aRec As Worksheet, bRec As Worksheet, wb As Workbook
Dim aDesc As String, bDesc As String
Dim auxSheet1 As Variant
Dim auxSheet2 As Variant
Dim count As Integer
Set wb = Excel.ActiveWorkbook
Set aRec = wb.Worksheets(1)
Set bRec = wb.Worksheets(2)
For i = 2 To aRec.UsedRange.Rows.count
count = 1
auxSheet1 = Trim(aRec.Cells(i, 1).Value)
For j = 2 To bRec.UsedRange.Rows.count
auxSheet2 = Trim(bRec.Cells(j, 4).Value)
If Not auxSheet1 = auxSheet2 Then
count = count + 1
End If
If count = bRec.UsedRange.Rows.count Then
aRec.Cells(i, 1).Value = ""
End If
Next j
Next i
End SubI have attached also the excel file so you guys can better check and see the meaning of it.
My problem is that this code works only when I am pressing the bottom "press" which is in the Excel file. Instead I want my code to be smarter and avoid pressing the bottom. What I mean is that every time when an entry is entered in sheet1 range(A2:A6) the code should work automatically and delete the entries which don't match from sheet2 range(d2:d6). I want to delete the bottom and let the code to do its job automatically every time a data is inserted in range(A2:A6). To keep the data that match, and delete the ones which don't.
I am new here so hope I made myself clear.
Last edited by a moderator:
