VBA Code to Clear Cell

[ERed1]

Hi all,

I am trying to write a VBA code that will search the column and clear are cell with a specific value. For example, it will search column B for a cell containing the number "1". If it finds a cell contain "1", then that cell gets its contents cleared. I don't want it deleted so other columns and cells don't shift.

Thanks in advance for the help.

 

[Fluff]

Do you want to clear all cells with 1?

 

[ERed1]

Do you want to clear all cells with 1?

Yes!

 

[Fluff]

Forgot to ask if the numbers are hard values, or formulae.
If hard values try

Sub ERed1()
   Range("B:B").Replace 1, "", xlWhole, , , , False, False
End Sub

 

[FrancisM]

What about cells where the value is greater than 1. I.e. 10, 11.....19?

 

[ERed1]

It threw an error code. In my actual sheet, I am having column AD format to a date. The date for some of the cells is "1/0/1900". When I changed 1 in the code to the date, it still did not work.

 

[ERed1]

What about cells where the value is greater than 1. I.e. 10, 11.....19?

The cells that aren't 1/0/1900 I want to stay the same.

 

[Fluff]

What was the error code & message?

 

[Fluff]

Assuming you have formatted the cell as m/d/yyyy then your cells contain 0 not 1

 

[ERed1]

The error code says
" Run-time error '11':
Division by zero"