Macro to add up values in unhidden rows and clear if zwero

[tonywatsonhelp]

Hi Everyone,

I need a macro that when run looks at the the data in Range D25:BZ65) and add up each row, if the value for a row is more than 0 then just leave it but if the value in that row is 0 then clear cell B of that row.

please help if you can

Tony

 

[JoeMo]

You didn't say what you want to happen if the value is less than zero so I'll assume your numeric content is such that the sum will always be 0 or greater.

Sub ClearB()
Dim R As Range, Rw As Range
Set R = Range("D25:BZ65")
For Each Rw In R.Rows
    If Application.Sum(Rw) = 0 Then
        If Rw.EntireRow.Hidden = False Then Rw.Cells(1, 1).Offset(0, -2).ClearContents
    End If
Next Rw
End Sub

 

[tonywatsonhelp]

Thanks JoeMo,
yes completely right can never be less than zero,

thanks very much for your help


Tony

 

[JoeMo]

Thanks JoeMo,
yes completely right can never be less than zero,

thanks very much for your help


Tony

You are welcome - thanks for the reply.

 

[tonywatsonhelp]

I just realised I made a terrible mistake in my description ArrH!!!!

So sorry Joe Mo your code was excellent but I got it wrong,

where I said "Range D25:BZ65" I forgot to say but only the columns that are not hidden!

basically I need the formula to sum only Unhidden cells

can this be edited?

 

[JoeMo]

I just realised I made a terrible mistake in my description ArrH!!!!

So sorry Joe Mo your code was excellent but I got it wrong,

where I said "Range D25:BZ65" I forgot to say but only the columns that are not hidden!

basically I need the formula to sum only Unhidden cells

can this be edited?

See if this does it:

Sub ClearB()
Dim R As Range, Rw As Range, c As Range, S As Double
Set R = Range("D25:BZ65")
For Each Rw In R.Rows
    S = 0
    For Each c In Rw.Cells
        If c.EntireColumn.Hidden = False Then S = S + c.Value
    Next c
    If S = 0 Then Rw.Cells(1, 1).Offset(0, -2).ClearContents
Next Rw
End Sub