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!

 

[fabriciofcmiranda]

I fixed the problem and now the code is working on the original workbook. But it keeps choking sometimes. From my understading, it chokes whenever the cells next to the code (on the same row that will be deleted) are empty. Since I don't understand what the criterias of the "Find" really mean (LookIn, LookAt, SearchOrder, etc.), I'd like to ask for help to check whether all the criteria are really right on this code:

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

I don't know if it's relevant, but the code has this format: 00.00.00 (where 0 could be any number from 0 to 9).

Thank you very much for the help!!!

 

[fabriciofcmiranda]

Can anyone help me with this one? I've been stuck on this same problem for over 2 weeks... I really need to understand why the macro works perfectly some times and chokes on other times... I'm sure the problem is on the "Find" statement... Thanks a lot in advance!

 

[lozzablake]

send the workbook to me at lozzablake@yahoo.com and I'll take a look

 

[fabriciofcmiranda]

loozablake, thank you so much for your help through e-mail! I just wanted to share here the solution:

The new code should be:

      Sheets("Budget Projects").Select
      Set rngFound = Range("budgetprojects_code").Find(What:=rng.Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
      rngFound.EntireRow.Delete

The problem was on the "After:=ActiveCell" part of the code, that looked for something on a range that could be wrong. I also changed the "LookIn:=xlFormulas" for "LookIn:=xlValues", since I'm looking for value and not a formula.

Once more, thank you so much, loozablake, for your help.

Best regards,
FM