austenr
10-17-2006, 11:51 AM
I need to delete rows from sheet1 if the value is found anywhere in the array on sheet2. So lets say that my value I am searching for is in column B. The array is in column A of sheet2. Can you use a function to do that or do you have to use a macro?
will this do what I want?
Sub compare_delete()
FinalRowSh1 = Worksheets("Sheet1").Range("G65536").End(xlUp).Row
FinalRowSh2 = Worksheets("Sheet2").Range("F65536").End(xlUp).Row
For i = FinalRowSh2 To 1 Step -1
For j = FinalRowSh1 To 1 Step -1
If Worksheets("DHT ALL").Cells(j, 7) = Worksheets("GHT ALL").Cells(i, 6) Then
Worksheets("Sheet1").Cells(j, 1).EntireRow.Delete
End If
Next j
Next i
End Sub
will this do what I want?
Sub compare_delete()
FinalRowSh1 = Worksheets("Sheet1").Range("G65536").End(xlUp).Row
FinalRowSh2 = Worksheets("Sheet2").Range("F65536").End(xlUp).Row
For i = FinalRowSh2 To 1 Step -1
For j = FinalRowSh1 To 1 Step -1
If Worksheets("DHT ALL").Cells(j, 7) = Worksheets("GHT ALL").Cells(i, 6) Then
Worksheets("Sheet1").Cells(j, 1).EntireRow.Delete
End If
Next j
Next i
End Sub