SOLVED: Clear Cells with Certain Values

[tjohn909]

Hi everyone,

This is probably easy but I have no VBA experience.

All I want to do is clear cells within a range that contain the values "n/a" or "n/m". The range is columns C,D,E,F,G and go from row 2 to 29,423 (you can see why i don't want to do it manually).

Thanks for any help!

[JBeaucaire]

This can be done with an empty column and a formula. Let's say column M...

1) In M1 put "key"
2) In M2 put this formula:

=SUM(OR(--ISNUMBER(MATCH({"n/a","n/m"},C2:G2,0))))

3) Copy that cell down the whole dataset to complete the key column.
4) Select column M
5) Turn on the Data > Filter > Autofilter
6) Do a custom filter on that column for greater than: 0
7) Highlight all the visible rows of data and delete them
8) Turn off the Autofilter
9) Clear column M

[JBeaucaire]

My apologies, you said clear CELLS, meaning no rows change, just cells empty?

[MarvinP]

Hi,
No VBA needed.

Select columns C:G and click on Ctrl+H, to bring up the Find and Replace dialog.

Make sure you are looking for Values (not Formulas) and replace #N/A with "" and do the same with n/m.

See if that does it for you.

[Marcol]

Why don't you just select Columns C:G then use Find/Replace

Replace:= n/a
Replace With:= (Leave blank)
Replace All

Same for n/m

This code will do it for Columns C:G, (almost straight from the macro recorder)

Please Login or Register  to view this content.

If the Columns can change then change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Select the Required Columns and run the amended macro

Hope this helps

[tjohn909]

Perfect. Thanks Everyone. didn't think of replace