Clear nulls cells in a range so they are blank

[ChristineJ]

This code clears the cells on a sheet that have a value of "" (null) so that they are blank.

Sub Macro2()
With ActiveSheet
   .UsedRange.Value = Evaluate("IF(ROW(" & .UsedRange.Address & "),CLEAN(" & .UsedRange.Address & "))")
End With
End Sub

Is there a way to do the same thing for a range of cells on a sheet rather than on the entire sheet? I am hoping for a way that does not involve looping from one row to the next, which takes a long time.

Thanks

 

[Fluff]

Are the "" as the result of a formula that has been cut/pasted as values?

 

[ChristineJ]

Not necessarily cut and paste, and not necessarily values.

Could just be a formula right within the cell, such as if(G1=5,"",100) where G1 does equal 5.

Just want to convert a null to a blank. Sorry to have been unclear.

 

[Fluff]

You do realise that your code will remove the formulae from the cells?
Is that what you want?

 

[ChristineJ]

You do realise that your code will remove the formulae from the cells?
Is that what you want?

Yes, that is what I want - for the cells in a specified range (such as A1:A2000, for example) to be cleared so they are completely blank.

(The code I posted clears all the cells on the sheet - not what I need - but it seems to do it fast without looping.) Thanks again.

 

[Fluff]

In that case you can use

With ActiveSheet.Range("A1:A20")
   .Value = .Value
End With

 

[ChristineJ]

Yes, that does it, and quickly! I appreciate your help so much. C

 

[Fluff]

You're welcome & thanks for the feedback.