PDA

View Full Version : Solved: fill blank/black cells with 0



ami3
06-06-2008, 11:48 AM
Hi,
I need to fill black cells in my excel spread sheet with 0(zero) value.Please let me know how could i do that.Thanks a lot.

Ago
06-06-2008, 11:52 AM
all of them??
are you sure?
or do you have a range you want filled?

ami3
06-06-2008, 11:57 AM
all of them??
are you sure?
or do you have a range you want filled?

i have lots of numeric values in a spread sheet there are lots of random black cells are there,so its very time consuming putting zero by selecting each cell.

grichey
06-06-2008, 11:59 AM
black or blank?

grichey
06-06-2008, 12:08 PM
Sub setToZero()
Dim myRange As Object


Set myRange = _
Application.InputBox("Range?", _
"Range?", Type:=8)
For Each x In myRange
If IsEmpty(x.Value) Then
x.Select
Selection = "0"

End If

Next x
End Sub

Bob Phillips
06-06-2008, 03:25 PM
Sub setToZero()
Dim myRange As Object


Set myRange = _
Application.InputBox("Range?", _
"Range?", Type:=8)
For Each x In myRange
If IsEmpty(x.Value) Then
x.Select
Selection = "0"

End If

Next x
End Sub



Sub setToZero()
Dim myRange As Range

Set myRange = Application.InputBox("Range?", "Range?", Type:=8)
myRange.SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

ami3
06-06-2008, 04:53 PM
thanks a lot it works.Thanks again. Ami



Sub setToZero()
Dim myRange As Object


Set myRange = _
Application.InputBox("Range?", _
"Range?", Type:=8)
For Each x In myRange
If IsEmpty(x.Value) Then
x.Select
Selection = "0"

End If

Next x
End Sub

ami3
06-06-2008, 04:54 PM
Thanks a lot. Ami




Sub setToZero()
Dim myRange As Range

Set myRange = Application.InputBox("Range?", "Range?", Type:=8)
myRange.SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

Ago
06-06-2008, 10:06 PM
ahh! black.
i read blank thats why thought it was a strange request

grichey
06-06-2008, 10:08 PM
He did mean blank. Black is a pretty strange request too given the context.

mdmackillop
06-07-2008, 01:25 AM
Just for completion.

Sub Black()
Dim cel as Range
For Each cel In ActiveSheet.UsedRange
If cel.Interior.Color = vbBlack Then cel.Value = 0
Next
End Sub