Macro to find a value, select the row and delete it

[fabriciofcmiranda]

I have a big code that has a part that is supposed to look for the range value on a column, find it, select the whole row and delete it. But I just can't put it to work. This is the part of the code, considering rng as Range and "budget_code" as the part of column A where the macro needs to look for the value:

      Sheets("budget").Select
      Range("budget_code").Find(What:="rng.Value", After:=ActiveCell, LookIn:= _
         xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False, SearchFormat:=False).Activate
      rng.EntireRow.Delete

I'd appreciate if someone could help me on this one.

Thanks a lot!

 

[gaj104]

Find(What:="rng.Value",

Seems to me you are searching for the string rng.Value rather then the range's value.

If you remove the "" it should point you in the right direction

 

[fabriciofcmiranda]

I've tried it, but it still doesn't work. The code stops at this same sentence...

 

[lozzablake]

you need to Set rng and do not need quotes for the value. I'm assuming rng.value actually has a value. You may also want to put in an if statement to check if anything was actually found eg

set rng=Range("budget_code").Find(What:=rng.Value, After:=ActiveCell, LookIn:= _ 
         xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ 
         xlNext, MatchCase:=False, SearchFormat:=False).Activate 

if not rng Is Nothing then
    rng.EntireRow.Delete 
end if

 

[fabriciofcmiranda]

It still doesn't work. Trying to make it more clear, the code is supposed to do the following:

1. Ask for the client I want to delete on the "Clients" sheet;
2. When I select the client's code, it has to delete the whole line with information about that client and, then, go to sheet "Project Budget", look for the same client code and also delete the whole line of information.

Here's the whole code (without all the if statements):

Sub Delete_Clients()
    Dim rng As Range
    Sheets("Clients").Unprotect Password:="xxxxx"
    Sheets("Project Budget").Unprotect Password:="xxxxx"
    On Error Resume Next

    Set rng = Application.InputBox(prompt:="Select the code you want to delete", _
    Title:="Delete", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Not rng.Address = ActiveCell.Address Then rng.Select
    Else
      Exit Sub
    End If
    Ans = MsgBox("Are you sure you want to delete this client?", vbYesNo)
    If Ans = vbYes Then
      
      '***** HERE IS THE PART WHERE I NEED THE CODE *****
      Sheets("Project Budget").Select
      Range("projectbudget_code").Find(What:=rng.Value, After:=ActiveCell, LookIn:= _
         xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False, SearchFormat:=False).Activate
      rng.EntireRow.Delete
      
      Sheets("Clients").Select
      rng.EntireRow.Delete
      
    Else
      MsgBox ("The client with code " & rng.Value & " wasn't deleted from this list."), , "Attention"
      ActiveSheet.Protect Password:="xxxxx"
      Exit Sub
    End If

    Sheets("Clients").Protect Password:="xxxxx"
    Sheets("Project Budget").Protect Password:="xxxxx"
    
End Sub

Can anyone please help with this? Thank you very much in advance!

Best regards!

 

[lozzablake]

Your problem is that you have defined rng as the range where the original client choice is stored. You need to define a second range to hold the found range eg:

Sub Delete_Clients()
    Dim rng As Range        'range that holds target client
    Dim rngFound As Range   'range that holds the found range
    Dim Ans
    
    
    
    Sheets("Clients").Unprotect Password:="xxxxx"
    Sheets("Project Budget").Unprotect Password:="xxxxx"
    On Error Resume Next

    Set rng = Application.InputBox(prompt:="Select the code you want to delete", _
    Title:="Delete", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If Not rng Is Nothing Then
      If Not rng.Address = ActiveCell.Address Then rng.Select
    Else
      Exit Sub
    End If
    Ans = MsgBox("Are you sure you want to delete this client?", vbYesNo)
    If Ans = vbYes Then
      
      '***** HERE IS THE PART WHERE I NEED THE CODE *****
      Sheets("Project Budget").Select
      Set rngFound = Range("projectbudget_code").Find(What:=rng.Value, After:=ActiveCell, LookIn:= _
         xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
         xlNext, MatchCase:=False)
      rngFound.EntireRow.Delete
      
      Sheets("Clients").Select
      rng.EntireRow.Delete
      
    Else
      MsgBox ("The client with code " & rng.Value & " wasn't deleted from this list."), , "Attention"
      ActiveSheet.Protect Password:="xxxxx"
      Exit Sub
    End If

    Sheets("Clients").Protect Password:="xxxxx"
    Sheets("Project Budget").Protect Password:="xxxxx"
    
End Sub

 

[fabriciofcmiranda]

Thanks a lot for your reply! But the macro keeps choking on the same string:

Set rngFound = Range("budgetprojetos_codigo").Find(What:=rng.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

I really can't figure out the reason. I've been trying to set up this macro for over a week... I'd appreciate if you or anyone else could keep helping me on that.

 

[lozzablake]

thats odd it worked fine for me. Is there definitely a range with that name in the spreadsheet? And if there is, is it on the sheet Project budget sheet?

 

[fabriciofcmiranda]

Yes. Everything seems just fine. There's a sheet called "Project Budget" and, inside this sheet, there's the range called "budgetprojetos_codigo" (that means "projectbudget_code" in Portuguese), where all the codes are. The codes on sheet "Clients" are exactly the same as the ones on the sheet "Project Budget". So, I really don't understand what might going wrong...

 

[fabriciofcmiranda]

I tested your code on a blank workbook and it worked perfectly. I'll try to understand where's the error on the original workbook. Thank you very much for your help!