Change all numbers to zero

[FrankM]

OK, this request may seem a bit odd but please bear with me.

I have a couple dozen spreadsheets and I need to change all the numbers on
all the spreadsheets to zero.

Is it possible, with a Macro (or other means) to change all the numbers on
all the open spreadsheets to zero automatically vs manually.

Kindda a strange request, I know.

 

[Gary''s Student]

Sub zeroo()
For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
If IsNumeric(r.Value) Then
r.Value = 0
End If
Next
End Sub

 

[Mike H]

Hi,

Every sheet in every open workbook!! Hmm drastic indeed

This goes in a general module

Sub Drastic_Action()
Dim wbk As Workbook
Dim ws As Worksheet
For Each wbk In Workbooks
For x = 1 To wbk.Worksheets.Count
On Error Resume Next
For Each c In Sheets(x).UsedRange.SpecialCells(xlCellTypeConstants,
1)
c.Value = 0
Next
Next
Next wbk
End Sub

Mike

 

[JLGWhiz]

I believe the OP only wanted cells that had values to be changed to zero.
Without some qualifications, IsNumeric() will read an empty cell as Numeric
and change it to zero even though it had no value in it.

 

[JLGWhiz]

empty by any other name is empty. having nothing in it, including formuals
that return a zero lenth string ("") that would appear to be empty.

 

[Gord Dibben]

Simon

Open a new blank workbook.

On sheet1 enter 1 to 5 in A1:A5

Enter "I'll be darned" in H20

Run your macro.

What are results?


Gord Dibben MS Excel MVP

 

[keiji kounoike]

Try this one

Sub allnum2zero()
On Error Resume Next
Cells.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) = 0
End Sub

keiji

 

[keiji kounoike]

I forgot about all th spreadsheets part. Try this modified one.

Sub allnum2zero()
Dim sh As Worksheet
On Error Resume Next
For Each sh In Worksheets
sh.Cells.SpecialCells(xlCellTypeConstants, xlNumbers) = 0
sh.Cells.SpecialCells(xlCellTypeFormulas, xlNumbers) = 0
Next
End Sub

keiji