Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting and deleting a row based on a cell value
What code do I have to use to read through a column of cells and if a cell
has a certain value, it will delete the entire row. I have this code, but it keeps moving down a space after it deletes the row. For instance, I want to delete an entire row if the cell value is "2", but if there are numerous "2"s in a row, it always skips everyother one. Ex: 1,1,2,2,2,2 the result will show remaining cells 1,1,2,2. Here is what I have so far: Sub (rowdelete) For Each rw In Worksheets("sheet1").Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.delete last = this Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting and deleting a row based on a cell value
Hi JJ
You loop must start on the end of your data and go up See this page http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl "JJ" wrote in message ... What code do I have to use to read through a column of cells and if a cell has a certain value, it will delete the entire row. I have this code, but it keeps moving down a space after it deletes the row. For instance, I want to delete an entire row if the cell value is "2", but if there are numerous "2"s in a row, it always skips everyother one. Ex: 1,1,2,2,2,2 the result will show remaining cells 1,1,2,2. Here is what I have so far: Sub (rowdelete) For Each rw In Worksheets("sheet1").Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.delete last = this Next End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting and deleting a row based on a cell value
Sub macdelrow()
Dim t As Range Dim n As Range Set t = Range("a1") Do While Not IsEmpty(t) Set n = t.Offset(1, 0) If t.Value = 2 Then t.EntireRow.Delete shift:=xlUp End If Set t = n t.Select Loop End Sub -----Original Message----- What code do I have to use to read through a column of cells and if a cell has a certain value, it will delete the entire row. I have this code, but it keeps moving down a space after it deletes the row. For instance, I want to delete an entire row if the cell value is "2", but if there are numerous "2"s in a row, it always skips everyother one. Ex: 1,1,2,2,2,2 the result will show remaining cells 1,1,2,2. Here is what I have so far: Sub (rowdelete) For Each rw In Worksheets("sheet1").Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.delete last = this Next End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting and deleting a row based on a cell value
Hi,
Not sure i get it but maybe you could first use a range object to keep track of the rows to delete within the loop then delete them all at once after the loop. This way, you don't have to deal with adjusting the code for deleted rows every time. Something like: Dim RgRows as Range '... For Each rw In Worksheets("sheet1").Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then if RgRows Is Nothing then '<----------- set rgRows= rw else set rgrows = application.intersect( rgrows, rw) end if end if last = this Next rgRows.EntireRow.Delete '<-------------- Regards, Sebastien "JJ" wrote: What code do I have to use to read through a column of cells and if a cell has a certain value, it will delete the entire row. I have this code, but it keeps moving down a space after it deletes the row. For instance, I want to delete an entire row if the cell value is "2", but if there are numerous "2"s in a row, it always skips everyother one. Ex: 1,1,2,2,2,2 the result will show remaining cells 1,1,2,2. Here is what I have so far: Sub (rowdelete) For Each rw In Worksheets("sheet1").Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.delete last = this Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a cell entry based on cell validation selection | Excel Worksheet Functions | |||
Selecting A Column based on Cell Value | Excel Discussion (Misc queries) | |||
Selecting rows based on cell entries | Excel Discussion (Misc queries) | |||
Selecting a filepath based on cell contents | Excel Discussion (Misc queries) | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming |