Deleting a row containing a found cell

[Alan]

After finding a cell using "Find", how do I delete the
entire row?

Regards,
Alan

 

[Frank Kabel]

Hi
if you have assignet the found cell to a range object use something
like
found_cell.entirerow.delete

 

[Chip Pearson]

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

 

[Guest]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

Or you could've changed the order in your modified code:

Sub DeleteNARows3()
Dim FoundCell As Range
Set FoundCell = Cells.Find("N/A")
Do While Not (FoundCell Is Nothing)
FoundCell.EntireRow.Delete
Set FoundCell = Cells.Find("N/A")
Loop
End Sub

(Find the next after you've deleted the previous. But I'd still stick in all
those .find options.)

 

[Alan]

But when you delete that row, then FoundCell doesn't
point at anything. You
You're right! I've been deleting objects for years, in
Access and Word. But this is the first time that I've
actually deleted a real-world, live object that I can
watch being deleted

The books always say that objects should be from the "real
world", but in fact most objects only live in system
memory. So deleting them usually means "Set BillGates =
Nothing". (This doesn't work
-------------

Since I'm new to Excel programming, that didn't occur to
me. But exactly the same thing happens in Word.
-------------

Thanks for your help.

Regards,
Alan