Chip's code works fine if you're only deleting the first instance:
Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If Not FoundCell Is Nothing Then
FoundCell.EntireRow.Delete
End If
But if you try to wrap it into a do loop like:
Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
do while not (foundcell is nothing)
If Not FoundCell Is Nothing Then
FoundCell.EntireRow.Delete
End If
loop
The first time through the code, Foundcell will represent the first found cell.
But when you delete that row, then FoundCell doesn't point at anything. You
have to tell it to go find another cell.
So your extra "set FoundCell" finds that next cell.
I like this better (but it's personal preference for the most part):
Option Explicit
Sub DeleteNARows2()
Dim FoundCell As Range
Do
Set FoundCell = Cells.Find("N/A", LookIn:=xlFormulas, _
lookat:=xlPart, MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End Sub
I don't have the extra "set foundcell" outside the loop.
But watch out for the .find stuff. Excel likes to remember the last settings
you've used. And if you don't explicitly set them the way you want, those last
settings will be used.
wrote:
Chip,
Your code works fine when I execute it once.
If I create a Do loop, the code works only once if I don't
have the commented line.
Why do I need the commented line?
Thanks for your help.
Regards,
Alan
--------
Sub DeleteNARows()
Dim FoundCell As Range
Set FoundCell = Cells.Find("N/A")
Do While Not (FoundCell Is Nothing)
Set FoundCell = Cells.Find("N/A") 'Why needed?
FoundCell.EntireRow.Delete
Loop
End Sub
------------
-----Original Message-----
Alan,
Try something like
Dim FoundCell As Range
Set FoundCell = Cells.Find(....)
If Not FoundCell Is Nothing Then
FoundCell.EntireRow.Delete
End If
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Alan" wrote in
message
...
After finding a cell using "Find", how do I delete the
entire row?
Regards,
Alan
.
--
Dave Peterson