I'm assumiing you have more than one occuarance of the number you are
searching for. When multiple rows from a large range of data it is much
faster to put an X in an auxilarary column then using autofilter to find
the x's and deleting all the rows with a single instruction like below.
the FIND method is much faster than manually going through the date.

when deleting rows witout using the method below you would get the last
row and move backwards through the data like my Second Example. Yo can
run both macros and compare the execution time.




LastRow = Range("B" & Rows.Count).End(xlUp).Row

Set DataRange = Range("B3:B" & LastRow)

Data = Range("B2")

Set c = DataRange.Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddr = c.Address
Do
'put X in column IV for rows to delete
Range("IV" & c.Row) = "X"
Set c = DataRange.FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
'insert row 1 temporarily to allow autofilter to work properly
Rows(1).Delete
Columns("IV:IV").AutoFilter
Columns("IV:IV").AutoFilter Field:=1, Criteria1:="X"
Columns("IV:IV").SpecialCells(xlCellTypeVisible).D elete
'remove the autofilter by deleting the column
Columns("IV:IV").Delete
'delete row 1 which was added
Rows(1).Delete

End If


End Sub


Sub DeleteRows2()

LastRow = Range("B" & Rows.Count).End(xlUp).Row
Data = Range("B2")
RowCount = LastRow
Do While RowCount = 3

If Data = Range("B" & RowCount) Then
Rows(RowCount).Delete
End If

RowCount = RowCount - 1
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165092

Microsoft Office Help