VBA - find then delete data/row

[george hart]

Hi

The code below works fine, in that you can enter data and it will find it and delete the rows. However, it falls over when you enter data that isn't there.

Any way I can get a message pop up when this happens to say data not found and then end sub??

Dim Answer As String
Answer = MsgBox("Are you sure you want to remove a KPI?", vbYesNo)
If Answer <> vbYes Then Exit Sub
data = (InputBox("Enter the KPI you want to remove"))
Sheets(Array("Actual Operations KPI", "Targets Operations KPI", _
"Data provider - Definition")).Select
Sheets("Actual Operations KPI").Activate
Cells.Find(What:=(data), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.EntireRow.Delete
Sheets("Current Period-YTD Ops KPI").Select
Cells.FindNext(After:=ActiveCell).Activate
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp

 

[Gary McMaster]

You could try an error handler. Maybe something like so:

Public Sub Test()
 
Dim Answer As String
 
On Error GoTo MyError
 
Answer = MsgBox("Are you sure you want to remove a KPI?", vbYesNo)
If Answer <> vbYes Then Exit Sub
Data = (InputBox("Enter the KPI you want to remove"))
Sheets(Array("Actual Operations KPI", "Targets Operations KPI", _
"Data provider - Definition")).Select
Sheets("Actual Operations KPI").Activate
Cells.Find(What:=(Data), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
ActiveCell.EntireRow.Delete
Sheets("Current Period-YTD Ops KPI").Select
Cells.FindNext(After:=ActiveCell).Activate
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlUp
 
Exit Sub
 
MyError:
 
MsgBox "Data not found"
 
End Sub

Gary

 

[george hart]

Many thanks

I managed to figure it out last night with what you advised

Thanks again

 

[Jon von der Heyden]

Generally it's a good idea to set a reference to a found cell. I.e.:

Dim rngFoundCell as Range

On Error Resume Next
    set rngFoundCell = Cells.Find(What:=myString, LookAt:=xlWhole)
On Error GoTo 0

Then there will be instances where the range object variable is Nothing (i.e. the search string was not found). You should check if it is Nothing before invoking the rest of the code. I.e:

If Not rngFoundCell is Nothing Then
    'do something
End If

I don't believe you should mask the errors throughout your code...