Easy replace macro

[Leon_UK]

Hi all.

I want to create a macro which will replace all cells in a selected range with '0' Zero.

all help welcome?

Cheers

 

[Leon_UK]

Update.

I've created this:

Sub Change_Blanks()
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("A1:D10").Cells
    
    For Each cell In myRange
        If cell.Value = "" Then cell.Value = 0
    Next
End Sub

How do I remove the need to specify a range so works with any selected cells I have when the macro is ran?

 

[Richard Schollar]

Hi

Dim cell As Range
For Each cell in Selection
  If IsEmpty(cell.Value) Then cell.Value = 0
Next cell

 

[Richard Schollar]

Altho, if it's blanks your targeting, you have an faster option:

Dim rBlanks As Range
On Error Resume Next
Set rBlanks = Selection.SpecialCells(xlCellTypeBlanks)
On Error Goto 0
If Not rBlanks Is Nothing Then rBlanks.Value = 0