Search and delete row
 

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.


--
Thanks
Shawn

Search and delete row
 

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

Search and delete row
 

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shawn" wrote in message
...
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.


--
Thanks
Shawn

Search and delete row
 

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:

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Shawn" wrote in message
...
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.


--
Thanks
Shawn

.