Delete Macro if Row contains Value in Column B

[MagicMan]

Greetings Hive mind,

I have a spreadsheet where the value in cell C5 of the Tab called "input" influences the value in Column B of the tab called Impacted Sheet

I'm looking for a macro that, when activated, will look for the value "N/A" in column B and, if it is present, will delete the entire row.

There are three possible states for the "input cell", DP, DC and JDC

In my example workbook I've included the desired output for each state. These are for example purposes as the changes would be made on the "Impacted Sheet" Tab

Many thanks and have a wonderful weekend.

MM

[sintek]

Please Login or Register  to view this content.

[rollis13]

Hi to all.
Oh, I'm terribly late, anyway, this is what I came up with:

Please Login or Register  to view this content.

[MagicMan]

Hi Sintek - you help me again

I see it works by doing a row count. Just as an aside, how would the coding be changed if I only wanted it to do it on a certain number of rows. For example, if I wanted to keep rows 39 through to 43 irrespective of the outcome?

Thanks

MM

[sintek]

Play around with the red snippet [-1] in code above

[MagicMan]

Hi Rollis,

Nearly there I do appreciate your efforts.

With your code I have found that it doesn't influence cell b3 and that gets deleted irrespective of what the value may be.

[MagicMan]

Brilliant Sintek!

That works, but it deletes the value in Column A, any ideas on how I can retain that?

MM

P.S. Rep added :-)

[sintek]

but it deletes the value in Column A

Not sure what you mean...?
Can only assume your "MERGED CELLS" is causing havoc...
Hence rule No 1 in Excel...Do Not Use Merged Cells...
Also why rows 1 and 2 blank...No Headers...Poor sheet setup practice

[MagicMan]

Hence rule No 1 in Excel...Do Not Use Merged Cells...

Fair Point, well made

Also why rows 1 and 2 blank...No Headers...Poor Sheet setup practice

It's blank in the example, on my 'working sheet' it will have headers etc. I deliberately set the example in the way I do so that I can identify when something is coded as "B3:B" as opposed to "B:B". This helps me to understand the workings of the macro better and, hopefully, some of it will sink in to my aged brain and, who knows, it may even stay there for a little while

[sintek]

Future advice...Always upload a sample file depicting your actual file setup...99% of the time users are not able to edit code to fit actual file setup...
Thanks for rep +