VBA - Searching a value and deleting a row if found

[PaulFletcher]

Hi,

I have written the below code to search a value and if found remove the entire row and if successful pop a message advising the record has been deleted.

I have two problems:
1. When there is no data the message will still pop up confirming something has been deleted which isn't the case. When there is data the message pop's up twice?
2. I have attempted to add further code using "Else" to pop a further message advising if a record does not exist but also cannot get this to work.


With .Cells(Lrow, "A")

If Not IsError(.Value) Then

If .Value = ThisWorkbook.Sheets("Input").Range("B21") Then .EntireRow.Delete
MsgBox "Record Deleted"


End If

End With


Any help would be much appreciated.

Thanks

Paul

 

[alansidman]

Suggest you tell us in simple English what you are attempting to do if you were to do this manually and then we can provide you with some efficient code.

 

[PaulFletcher]

Thanks Alan.

I am attempting to search a value and if it exists remove it and the row it is in from a separate worksheet.
If this is successful a message box will appear confirming thios has been deleted
If this is unsuccessful (i.e. the value does not exist) a message will appear stating the record is not found.

Hope that makes sense...

Thanks

Paul

 

[dmt32]

hi,
not showing all your code but as a guess try:

     Dim FoundCell As Range    
     Dim Search As String


    Search = ThisWorkbook.Sheets("Input").Range("B21").Value
    
    Set FoundCell = Columns(1).Find(Search, lookat:=xlWhole, LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        FoundCell.EntireRow.Delete
        MsgBox Search & Chr(10) & "Record Deleted", 48, "Record Deleted"
    Else
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If

Dave

 

[alansidman]

deleted. Misposted response

 

[PaulFletcher]

Thanks Dave

I have tried this and have successfully popped a message box if tghe record is deleted or not; however if it is deleted it pops both messages?
I have put my code in full which i apologise for a\s has been taken from a few separate sources so probably isn;t perfect by any means!

Sub SearchBookings()


Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    
    Dim FoundCell As Range
    Dim Search As String
     
    Set bookings = Worksheets("Bookings Data")
    Set inputdata = Worksheets("Input")
    
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With


    With bookings
        .Select
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView
        .DisplayPageBreaks = False
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


        For Lrow = Lastrow To Firstrow Step -1


            Search = ThisWorkbook.Sheets("Input").Range("B21").Value
    
        Set FoundCell = Columns(1).Find(Search, lookat:=xlWhole, LookIn:=xlValues)
             If Not FoundCell Is Nothing Then
            FoundCell.EntireRow.Delete
            MsgBox "Record Deleted"
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
            End If
                               
    
     
        Next Lrow


       ActiveWindow.View = ViewMode
    
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With




   
            With Sheets("Input").Select
                    Range("B21").Select
                    Selection.ClearContents
            End With
    
End With
End Sub

 

[dmt32]

Hi,

you do not need to place code in For next Loop

  For Lrow = Lastrow To Firstrow Step -1




        Search = ThisWorkbook.Sheets("Input").Range("B21").Value
    
        Set FoundCell = Columns(1).Find(Search, lookat:=xlWhole, LookIn:=xlValues)
             If Not FoundCell Is Nothing Then
            FoundCell.EntireRow.Delete
            MsgBox "Record Deleted"
        Else
            MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
            End If
                               
    
     
 Next Lrow

your values are not applicable to my suggestion - delete all code shown in RED

Dave

 

[PaulFletcher]

Thats perfect Dave
Thank you for your help