0

I am trying to find and delete a row containing a specific value. For this, I use the following excel-VBA code but it shows an error and hence finds nothing (shows rfnd=nothing). My code is -

Sub Save_Data_on_Master_Sheet()
'''' Save Data on Master Sheet''''
Dim Str As String, rfnd As Range

Str = Sheets("MASTER_DATA").Range("P2")

Sheets("MASTER_DATA").Range("D5:D2000").Select
Set rfnd = Selection.Find(Str, After:=Sheets("MASTER_DATA").Range("D4"), LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
rfnd.EntireRow.Delete

End Sub

Links of screenshots https://www.dropbox.com/s/v0zfs575mjqz7ve/Capture.JPG?dl=0 https://www.dropbox.com/s/3hxrpbcj1pjx7oc/Capture.JPG2.JPG?dl=0

Could anyone tell me where I did make a mistake!

Abhyudaya
  • 13
  • 3
  • 1
    That means the `Find` didn't find anything in that range... are you sure there's a match? – BigBen Feb 04 '20 at 13:58
  • Yes, there is a value in cell D6 – Abhyudaya Feb 04 '20 at 13:59
  • 2
    Any chance you can share a sample screenshot or add some sample data? – BigBen Feb 04 '20 at 14:00
  • 1
    `Sheets("MASTER_DATA").Range("D5:D2000").Select`, scratch that, and set a `Range` object. This will throw an error on you the minute `MASTER_DATA` isn't the `ActiveSheet`. As per weather or not you should find a match, inspect your data and make sure there are no leading/trailing spaces throwing off `Range.Find` – JvdV Feb 04 '20 at 14:04
  • Don't know how to add photo in a comment. – Abhyudaya Feb 04 '20 at 14:05
  • You can [edit] your original question. – BigBen Feb 04 '20 at 14:06
  • 3
    I think your `After` cell should be in the range being searched. – SJR Feb 04 '20 at 14:08
  • 1
    @SJR, yes it should, and should fix the error =) – JvdV Feb 04 '20 at 14:09
  • MASTER_DATA sheet is an ActiveSheet. In find section, LookIn:=xlValues, shows a value -4163. – Abhyudaya Feb 04 '20 at 14:14
  • 1
    @Abhyudaya - in `After:=Sheets("MASTER_DATA").Range("D4")` - `D4` is *outside* the `Range` being searched ("D5:D2000"). Either change that to a cell inside the range or get rid of the `After` parameter entirely. – BigBen Feb 04 '20 at 14:17
  • 1
    @Abhyudaya - it was SJR and please remember to accept the answer below by clicking the check mark to the left. – BigBen Feb 04 '20 at 14:26

1 Answers1

2

As per comment, immediate cause of your error was that your After parameter was set to be a cell outside the range being searched.

Below I have removed it altogether as in general it's not needed (at least not seemingly in this case).

You can also avoid Selecting.

And always check that your Find finds something before operating on the result to avoid an error.

Sub Save_Data_on_Master_Sheet()

Dim Str As String, rfnd As Range

With Sheets("MASTER_DATA")
    Str = .Range("P2").Value
    Set rfnd = .Range("D5:D2000").Find(Str, LookIn:=xlValues, _
                        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False)
    If Not rfnd Is Nothing Then rfnd.EntireRow.Delete
End With

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26