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

Regards,
Alan

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Alan" schrieb im Newsbeitrag
...
After finding a cell using "Find", how do I delete the
entire row?

Regards,
Alan

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

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


.

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

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.)



Dave Peterson wrote:

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


--

Dave Peterson

But when you delete that row, then FoundCell doesn't
point at anything. You
have to tell it to go find another cell.


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 :-)
-------------

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.

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



-----Original Message-----
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.)



Dave Peterson wrote:

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


--

Dave Peterson

.