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

fabriciofcmiranda

Board Regular
Joined
Jun 27, 2006
Messages
62
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:

Code:
      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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
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
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
Code:
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

Board Regular
Joined
Jun 27, 2006
Messages
62

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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:

Code:
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

Board Regular
Joined
Jun 27, 2006
Messages
62

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Board Regular
Joined
Jun 27, 2006
Messages
62
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

Board Regular
Joined
Jun 27, 2006
Messages
62
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!
 

Forum statistics

Threads
1,136,878
Messages
5,678,305
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top