Delete rows that cell value is "No"
 

Hi and thanks in advance.

I have a range from A1:A500. The cells have Yes and No...is there anyway to
adjust my code to work? I'm trying to delete the rows that have cell value
of no.

Dim cl As String

cl = "No"

For Each cl In Range("a1:a500")
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next cl

thank you.

Tim

Delete rows that cell value is "No"
 

Sub Tester
dim rCell as Range
For each rCell in range("A1:A500")
if rCell.value="no" then rCell.Entirerow.Delete
next rCell
end sub

HTH
--
AP

"Tim" a écrit dans le message de news:
...
Hi and thanks in advance.

I have a range from A1:A500. The cells have Yes and No...is there anyway
to
adjust my code to work? I'm trying to delete the rows that have cell
value
of no.

Dim cl As String

cl = "No"

For Each cl In Range("a1:a500")
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next cl

thank you.

Tim

Delete rows that cell value is "No"
 

Hi Ardus! Thank you very much, it's works perfectly :)

"Ardus Petus" wrote:

Sub Tester
dim rCell as Range
For each rCell in range("A1:A500")
if rCell.value="no" then rCell.Entirerow.Delete
next rCell
end sub

HTH
--
AP

"Tim" a écrit dans le message de news:
...
Hi and thanks in advance.

I have a range from A1:A500. The cells have Yes and No...is there anyway
to
adjust my code to work? I'm trying to delete the rows that have cell
value
of no.

Dim cl As String

cl = "No"

For Each cl In Range("a1:a500")
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next cl

thank you.

Tim

Delete rows that cell value is "No"
 

Hi Tim

The code Ardus posted is not correct because you must loop from the bottom up when you delete rows.
It will skip rows as it is now

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


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Tim" wrote in message ...
Hi Ardus! Thank you very much, it's works perfectly :)

"Ardus Petus" wrote:

Sub Tester
dim rCell as Range
For each rCell in range("A1:A500")
if rCell.value="no" then rCell.Entirerow.Delete
next rCell
end sub

HTH
--
AP

"Tim" a écrit dans le message de news:
...
Hi and thanks in advance.

I have a range from A1:A500. The cells have Yes and No...is there anyway
to
adjust my code to work? I'm trying to delete the rows that have cell
value
of no.

Dim cl As String

cl = "No"

For Each cl In Range("a1:a500")
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next cl

thank you.

Tim

Delete rows that cell value is "No"
 

Ron is definitely right:

Sub Tester()
Dim lRow As Long
For lRow = 500 To 1 Step -1
If Cells(lRow, "A").Value = "no" Then
Rows(lRow).Delete
End If
Next lRow
End Sub


HTH
--
AP

"Ron de Bruin" a écrit dans le message de news:
...
Hi Tim

The code Ardus posted is not correct because you must loop from the bottom
up when you delete rows.
It will skip rows as it is now

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


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Tim" wrote in message
...
Hi Ardus! Thank you very much, it's works perfectly :)

"Ardus Petus" wrote:

Sub Tester
dim rCell as Range
For each rCell in range("A1:A500")
if rCell.value="no" then rCell.Entirerow.Delete
next rCell
end sub

HTH
--
AP

"Tim" a écrit dans le message de news:
...
Hi and thanks in advance.

I have a range from A1:A500. The cells have Yes and No...is there
anyway
to
adjust my code to work? I'm trying to delete the rows that have cell
value
of no.

Dim cl As String

cl = "No"

For Each cl In Range("a1:a500")
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Next cl

thank you.

Tim