selecting and deleting a row based on a cell value

[JJ]

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

 

[Ron de Bruin]

Hi JJ

You loop must start on the end of your data and go up

See this page
http://www.rondebruin.nl/delete.htm

 

[Don Guillett]

Oft asked so the archives would have provided an answer. Go from the bottom
up.

Sub deltetestuff()
x = Cells(Rows.Count, "a").End(xlUp).Row
For i = x To 1 Step -1
if cells(i,"b")="Unused or cells(i,"c")=0 then
cells(i,"a").entirerow.delete
Next
End Sub

 

[Guest]

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

 

[sebastienm]

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