If True then DO, otherwise DON"T

M

MikeF

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
 
D

Dave Peterson

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

Gary''s Student

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
 
M

MikeF

Thanx!!

Gary''s Student said:
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
Click to expand...
 
R

Rick Rothstein

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
 
C

Chip Pearson

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

MikeF

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
 
D

Dave Peterson

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
 
R

Rick Rothstein

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
 
Top