Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keyword search and delete row | Excel Discussion (Misc queries) | |||
Search and Delete | Excel Programming | |||
SEARCH AND DELETE | Excel Programming | |||
Add-in to search and delete | Excel Programming | |||
Search and Delete | Excel Programming |