ExcelBanter - Find & delete rows with ID's from another sheet.

Hi plys

This example loop through all cells in the C column of the activesheet
and delete the whole row if the value is in a sheet named "Sheet2" in the range A1:A20

Sub Example1()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1

If IsError(.Cells(Lrow, "C").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf Not IsError(Application.Match(.Cells(Lrow, "C").Value, _
Sheets("Sheet2").Range("A1:A20"), 0)) Then .Rows(Lrow).Delete

End If
Next
End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

End Sub

More information here
http://www.rondebruin.nl/delete.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"plys" wrote in message ...
I need a way to find & delete rows in sheet €œBilled€ that contain €œClaimId€
numbers. The €œClaimID€ numbers that I need the rows deleted for, are listed
in a column of another sheet called €œVoidReq€. Continuing down the €œVoidReq€
sheet column until if finds an empty cell.

Hopefully its simple for someone€¦