Checking & Deleting

[drums4monty]

Hi

I have a workbook with 2 worksheets. In Col A of both sheets is a ref number. I need to check both sheets and if the ref number appears on both sheets, and there is no data (currency figure) in Col E of Sheet 1 delete the row, but if there is data (currency figure) in Col E of Sheet 1, keep it. The sheets could be of different row lengths.

Can anyone out there help?

Alan

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Set sh = Worksheets("Sheet2")
With Worksheets("Sheet1")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 1 Step -1

If Not IsError(Application.Match(.Cells(i, "A").Value, sh.Columns(1), 0)) Then

If .Cells(i, "E").Value <> "" Then

.Rows(i).Delete
End If
End If
Next i
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub
[/vba]

[drums4monty]

Thanks xld. Do I copy and then paste the code into a Module?

[Bob Phillips]

Yes, just cpy it into a standard code module, and run it from there.