This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
.... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

dim Foundcell as range
set foundcell = cells.find(...)
if foundcell is nothing then
'not found
else
'found
end if



MikeF wrote:

This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

--

Dave Peterson

Cells.Find retruns a Range not a Boolean. How about:

Sub RemoveAvgPricesBOD2()
Dim r As Range
Set r = Cells.Find(What:="Average Prices")
If r Is Nothing Then Exit Sub
r.EntireRow.ClearContents
End Sub
--
Gary''s Student - gsnu200821


"MikeF" wrote:

This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

Thanx!!

"Gary''s Student" wrote:

Cells.Find retruns a Range not a Boolean. How about:

Sub RemoveAvgPricesBOD2()
Dim r As Range
Set r = Cells.Find(What:="Average Prices")
If r Is Nothing Then Exit Sub
r.EntireRow.ClearContents
End Sub
--
Gary''s Student - gsnu200821


"MikeF" wrote:

This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

Thanx!!

"Dave Peterson" wrote:

dim Foundcell as range
set foundcell = cells.find(...)
if foundcell is nothing then
'not found
else
'found
end if



MikeF wrote:

This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

--

Dave Peterson

Since you are only attempting to do one Find operation and nothing else
after it, you could what you want this way...

Sub RemoveAvgPricesBOD()
On Error Resume Next
Cells.Find(What:="Average Prices").EntireRow.ClearContents
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

Try something like the following:


Sub AAA()
Dim RowNdx As Long
Dim LastRow As Long
Dim WS As Worksheet
Dim FoundCell As Range
Set WS = Worksheets("Sheet1") '<<< CHANGE Sheet name
With WS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For RowNdx = LastRow To 1 Step -1
Set FoundCell = Nothing
Set FoundCell = _
.Cells(RowNdx, "A").EntireRow.Find _
(what:="average prices")
If Not FoundCell Is Nothing Then
.Rows(RowNdx).Delete
End If
Next RowNdx
End With
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sun, 21 Dec 2008 07:37:21 -0800, MikeF
wrote:

This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

That is the fundamentally simple answer.
Thank you.


"Rick Rothstein" wrote:

Since you are only attempting to do one Find operation and nothing else
after it, you could what you want this way...

Sub RemoveAvgPricesBOD()
On Error Resume Next
Cells.Find(What:="Average Prices").EntireRow.ClearContents
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

Thanx again, Rick.
One more query --- What if there were multiple rows that contain "Average
Prices" ...
How would I select all of them at the same time?
Regards,
- Mike

"Rick Rothstein" wrote:

Since you are only attempting to do one Find operation and nothing else
after it, you could what you want this way...

Sub RemoveAvgPricesBOD()
On Error Resume Next
Cells.Find(What:="Average Prices").EntireRow.ClearContents
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub

I'd use:

dim Foundcell as range
do
set foundcell = cells.find(...)
if foundcell is nothing then
exit do 'done looking
else
foundcell.entirerow.clearcontents
end if
loop
end if

MikeF wrote:

Thanx again, Rick.
One more query --- What if there were multiple rows that contain "Average
Prices" ...
How would I select all of them at the same time?
Regards,
- Mike

"Rick Rothstein" wrote:

Since you are only attempting to do one Find operation and nothing else
after it, you could what you want this way...

Sub RemoveAvgPricesBOD()
On Error Resume Next
Cells.Find(What:="Average Prices").EntireRow.ClearContents
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub




--

Dave Peterson

Then you would need a loop and more complex code...

Sub RemoveAvgPricesBOD()
Dim C As Range
Dim FirstAddress As String
On Error GoTo NotFound
Set C = Cells.Find(What:="Average Prices")
If Not C Is Nothing Then
FirstAddress = C.Address
Do
C.EntireRow.ClearContents
Set C = Cells.FindNext(C)
Loop While Not C Is Nothing And C.Address < FirstAddress
End If
NotFound:
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
Thanx again, Rick.
One more query --- What if there were multiple rows that contain "Average
Prices" ...
How would I select all of them at the same time?
Regards,
- Mike

"Rick Rothstein" wrote:

Since you are only attempting to do one Find operation and nothing else
after it, you could what you want this way...

Sub RemoveAvgPricesBOD()
On Error Resume Next
Cells.Find(What:="Average Prices").EntireRow.ClearContents
End Sub

--
Rick (MVP - Excel)


"MikeF" wrote in message
...
This should be very simple, but can't seem to make it work properly ..
The following procedure is called from another as application.run.
All it needs to do is delete the only row that contains the text
Average
Prices.
... If the text Average Prices doesn't exist, don't delete anything/do
nothing and move on.
Thanx in advance for any assistance.
- Mike

Sub RemoveAvgPricesBOD()

On Error Resume Next
If Cells.Find(What:="Average Prices") = True Then
Cells.Find(What:="Average Prices").EntireRow.ClearContents
If Cells.Find(What:="Average Prices") = False Then
Exit Sub
End If
End If

End Sub