By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,497 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,497 IT Pros & Developers. It's quick & easy.

I need a Excel macro to delete zeros only and no zeros containing more numbers.

P: 5
Hello everybody!

I need your valuable help to get an Excel macro to delete the single zeros only and no the zeros containing numbers like 360, 90, etc., because if I chose the search and replace in the Edit option, then all the zeros containing the numbers 360, 90, 502, etc. will be deleted.

Some nice person helped me with the following code to delete the zeros:
Selection.Replace What:="0", Replacement:="" , but unfortunately this code delete all the zeros including the zeros containing more numbers.

Thank you very mucho for your help.
Feb 5 '08 #1
Share this Question
Share on Google+
3 Replies


P: 11
If you want to use a macro to delete the zeros then you can use the following code
Expand|Select|Wrap|Line Numbers
  1. For Each cell In Selection
  2.     If cell = "0" Then
  3.         cell.Delete
  4.     End If
  5. Next
  6.  
This will go through all of the selected cells and delete any that are zero.

However you can also use search and replace edit option that you described, just make sure to check the "match entire cells contents".

Wagz
Feb 5 '08 #2

kadghar
Expert 100+
P: 1,295
If you want to use a macro to delete the zeros then you can use the following code
Expand|Select|Wrap|Line Numbers
  1. For Each cell In Selection
  2.     If cell = "0" Then
  3.         cell.Delete
  4.     End If
  5. Next
  6.  
This will go through all of the selected cells and delete any that are zero.

However you can also use search and replace edit option that you described, just make sure to check the "match entire cells contents".

Wagz
That's right, Excel has the option to mach entire cells content, The code wagz wrote will help, but will delete the cell. If what you want is to delete the zero, but keep the cell empty, instead of

cell.delete

use
cell.value = ""

HTH
Feb 6 '08 #3

P: 5
Hello guys!

I just want to thank you very much for your help!

I am very grateful with you!

I work with statistics most of the time and when I use the function to calculate the average the results vary if the cells have zeros and, of course, that affects the results.

So, the code is doing what I really need!

Have a very nice day!

God bless you!
Feb 6 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.