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
EscanabaAsked:
Who is Participating?
 
etech0Commented:
Do you want a zero in every blank cell, or zeros if the whole row is blank?

(option 1:)

Sub AddZeros()
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:)

Sub AddZerosRow()
dim addz as boolean
addz = true
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
addz = true
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
addz = true
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
 
gtglonerCommented:
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

Open in new window

0
 
StephenJRCommented:
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

Open in new window

0
 
EscanabaAuthor Commented:
Option 2 did the trick.  Thank you for the quick response.
0
 
etech0Commented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.