Search and delete row

S

Shawn

I have a table of data. I need a code that will search the column range
B3:B? (I don't know where the data will end). If there is a value in range
B3:B? that equals the value in B2, then I need that entire row deleted and
the rest of the data shifted up. Also, there may not be a value in B3:B?
equal to the value in B2. In that case, I need nothing to happen. Thank in
advance.
 
J

joel

I'm assumiing you have more than one occuarance of the number you ar
searching for. When multiple rows from a large range of data it is muc
faster to put an X in an auxilarary column then using autofilter to fin
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 las
row and move backwards through the data like my Second Example. Yo ca
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).Delete
'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 Su
 
D

Don Guillett

sub deleterowifb2()
dim i as long
for i=cells(rows.count,"b").end(xlup).row to 3 step-1
if cells(i,"b")=cells(2,"b") then rows(i).delete
next i
end if
 
E

excelent

another way

Sub Terminator()
x = Cells(65000, 2).End(xlUp).Row
Range("B3:B" & x).Replace what:=Range("B2"), Replacement:="", _
lookat:=xlPart, SearchOrder:=xlByRows
Range("B3:B" & x).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End Sub


"Don Guillett" skrev:
 
Top