Solved: Delete rows based on values in an array

[austenr]

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?

[vba] 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 [/vba]

[mdmackillop]

Hi Austen
Rather than looping both sets of data, use Find to determine if the data exists.
[VBA]
Option Explicit
Sub DelRows()
Dim Cel As Range, c As Range
With Sheets(1)
For Each Cel In Range(.Cells(1, 2), .Cells(Rows.Count, 2).End(xlUp))
Set c = Sheets(2).Columns(1).Find(Cel.Text)
If Not c Is Nothing Then Cel.EntireRow.Delete
Next
End With
End Sub

[/VBA]

[austenr]

Thank you my friend. I shall give it a go.

[austenr]

Hi Malcomb,

I tried your code on a sample spreadsheet which I am attaching. Some of the rows are still left on sheet1 after you run the macro. Any ideas?

[mdmackillop]

We need to delete from the bottom up!
Try
[VBA]
Sub DelRows()

Dim c As Range
Dim Rw As Long, i As Long, tmp As Long
Rw = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
For i = Rw To 1 Step -1
tmp = Sheets(1).Cells(i, 2).Value
Set c = Sheets(2).Columns(1).Find(tmp)
If Not c Is Nothing Then Sheets(1).Rows(i).Delete
Next
End Sub
[/VBA]

[austenr]

Thanks Malcomb. I realized that after fiddling with your code. Just had not had a chance to post back. Works great with the modification!! Many thanks. Solved