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

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

selecting and deleting a row based on a cell value
 

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



--
Don Guillett
SalesAid Software

"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

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
.

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