Find and replace contents

Ctrl+G ("go to") > Special > Constants, tick Numbers, press OK.

Put 0 in the formula bar, press and hold the Ctrl key, press Enter.

Your very brief explanation suggests to me that this is not possible without VBA. However...

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

try this macro

Please Login or Register  to view this content.

If you do need a macro, maybe try...

Please Login or Register  to view this content.

@Jeff what is the difference between Constant and the return of the function IsNumeric?
Your macro has a different outcome then mine. Yours is pretty cool.

Originally Posted by p24leclerc

@Jeff what is the difference between Constant and the return of the function IsNumeric?

I'm not aware of any difference between using the IsNumeric versus searching for Constants.

However, here are a few difference I see...

1) Looping thru all of the cells could be slower; whereas, the SpecialCells(xlCellTypeConstants, 1) does the update all in one pass

2) Using Range("A1").CurrentRegion -- This could cause problems, if for instance, columns B & C were empty and the next set of data starts in column D. For CurrentRegion to work, the set of data has to be contiguous

3) Maybe instead of Range("A1").CurrentRegion you could use ActiveSheet.UsedRange

Anyway, just a few thoughts. Hope it helps.

Glad it worked out for you.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.