Stumped - Delete Row based on criteria

[dubcap01]

I am having a problem deleting any rows in a worksheet where the desired text to look for is "Inactive". The code below correctly identifies a row, changes the value in Columnb J to "Inactive", and then I want to delete that same row, but the second part of the code to delete the row isn't working!

Please Login or Register  to view this content.

Can anyone enlighten me where I might be going wrong?

cheers
Pete

[quekbc]

When you say "isn't working" do you mean a VBA error or it doesn't delete the correct rows?

[dubcap01]

it doesn't delete the row - there is only ever one row to delete...

[MarvinP]

What is in column J?

Cells(i, 10) really can be "Cells(I, "J")

Are you really looking in column J for "Inactive"? Are there spaces before or after "Inactive"?

[quekbc]

Did a dummy sheet to test the code. The For Loop works fine on my dummy sheet.

Can you manually type in "Inactive" in column J of any row to test?

Also, you placed the word "Inactive" in a sheet called 'TreatmentOrders'. Can you confirm that when you run this macro that the active sheet is in the same sheet 'TreatmentOrders'? I note you have referred to Cells without the sheet qualifier.

[SPARKY347]

Try splitting the if and then statements into two seperate lines. See if it recognizes the "inactive cell" and jumps down to the delete line

[dubcap01]

Hi Marvin,

Yes, I am looking in column J for "Inactive", there are no leading or trailing spaces.
The previous four lines of code write "Inactive" (no punctuation or anything) into column J on the correct row... that bit checks out fine.

[dubcap01]

Hi queckbc

Earlier in the code I have this as a qualifier

Please Login or Register  to view this content.

[quekbc]

In that case, remember to put the fullstop "." before the Cells

I.e.

Please Login or Register  to view this content.

[MarvinP]

Is your active/selected sheet (the one that is visible) your
Sheets("TreatmentOrders")?

You can make it active by using a
Sheets("TreatmentOrders").Select just before your For..Next loop.

[dubcap01]

Hello all,

Finally made it work, with all your help! It's looking a bit of a mishmash but it's doing the job!
thanks everybody for your input and knowledge!

Please Login or Register  to view this content.