I have two lists which contains our products details. one of them contains our c

Question

I have two lists which contains our products details. one of them contains our current products (list0) and the other one (list1) contains updated products.

I need to crosscheck list0 and list1, find and delete entries in list0 which also in list1 too. but I get an error message when macro tried to set filter criteria.

below you can find my code, here's my steps and what I tried to aim;

open list1 (it contains UPDATED entry in column I)

run macro and choose list0

add a column and vlookup in list0

write UPDATED when found a product in list1 which also in list0

set filter for UPDATED and then delete those entries

thanks for your help

cem

Sub crosscheck()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim list0 As Workbook, list1 As Workbook
Dim vFile As Variant

Set list1 = ActiveWorkbook


vFile = Application.GetOpenFilename("Excel (*.xls; *.xlsx),*.xls;*.xlsx", _
    1, "Select File", , False)

If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
  
Set list0 = ActiveWorkbook


AutoFilterMode = False
AutoFilterMode = True



Columns("I:I").Select
Selection.Insert Shift:=xlToRight


Range("I2").Select
ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC8,[" & list0.Name & "]UE!C8:C9,2,0)"



Range("H3").End(xlDown).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).FillDown


ActiveSheet.Range("A1:V1").AutoFilter Field:=9, Criteria1:="UPDATED"
Range("A2:V1048576").SpecialCells(xlCellTypeVisible).EntireRow.Delete

  
Range("I65536").End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.EntireRow.Delete
ActiveSheet.Range("$A$1:$U$7").AutoFilter Field:=9
Range("A1").Select
Columns("I:I").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
'ActiveWorkbook.Save

list1.Activate


End Sub!

Explanation / Answer

It always helps if you tell us what the error message said.

Assuming there is data in all columns A to I and no blank rows within the data, I would replace the bold row and the one beneath it with

With Range("A1").CurrentRegion
.AutoFilter Field:=9, Criteria1:="UPDATED"
.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

I suspect the following 3 lines are an earlier version and should have been deleted.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects