# Excel 2007 Macro to Add Zeros To Blank Row

Hello,

Can someone assist in providing a macro that will look at sheet1, row A2:U2, and if that row is blank, add a zero in each of the cells within that range, then do the same for sheet2, and sheet 3?

Thanks!
LVL 1
###### Who is Participating?

Commented:
Do you want a zero in every blank cell, or zeros if the whole row is blank?

(option 1:)

for each cell in sheets("sheet1").range("A2:U2")
if cell.value = "" then cell value = "0"
next
for each cell in sheets("sheet2").range("A2:U2")
if cell.value = "" then cell value = "0"
next
for each cell in sheets("sheet3").range("A2:U2")
if cell.value = "" then cell value = "0"
next
End Sub

(option 2:)

for each cell in sheets("sheet1").range("A2:U2")
if cell.value <> "" then addz = false
next
for each cell in sheets("sheet1").range("A2:U2")
if addz then cell.value = "0"
next
for each cell in sheets("sheet2").range("A2:U2")
if cell.value <> "" then addz = false
next
for each cell in sheets("sheet2").range("A2:U2")
if addz then cell.value = "0"
next
for each cell in sheets("sheet3").range("A2:U2")
if cell.value <> "" then addz = false
next
for each cell in sheets("sheet3").range("A2:U2")
if addz then cell.value = "0"
next
End Sub
0

Commented:
Try this code in VBA:
``````Sub zero()

For Each cell In Range("A2:U2")
If cell.Value = "" Then
cell.Value = 0
End If
Next

End Sub
``````
0

Commented:
Perhaps this?
``````Sub x()

Dim ws As Worksheet

For Each ws In Worksheets
Select Case ws.Name
Case "Sheet1", "Sheet2", "Sheet3"
If WorksheetFunction.CountBlank(ws.Range("A2:U2")) = ws.Range("A2:U2").Count Then
ws.Range("A2:U2").Value = 0
End If
End Select
Next ws

End Sub
``````
0

Author Commented:
Option 2 did the trick.  Thank you for the quick response.
0

Commented: