PDA

View Full Version : Solved: How do you write this formula in VBA code?



genracela
05-24-2010, 05:07 PM
If column C to AX is equal to zero, clear contents.

Thanks!!!

ed in aus
05-24-2010, 05:31 PM
Sub test()
If WorksheetFunction.Sum(Range("C" & ActiveCell.Row & ":AX" & ActiveCell.Row)) = 0 Then
Range("C" & ActiveCell.Row & ":AX" & ActiveCell.Row).ClearContents
End If
End Sub

genracela
05-24-2010, 05:39 PM
It actually run, but zeros were not cleared.

And, why is there a "sum" function?

I just actually want to delete the zeros in the cells. I'm not summing up anything.

Thanks!

ed in aus
05-24-2010, 05:48 PM
Wasn't to sure what you were asking for, here is the code.



Sub test2()
For Each c In Range("C" & ActiveCell.Row & ":AX" & ActiveCell.Row)
If c.Value = 0 Then
c.ClearContents
End If
Next c
End Sub

mbarron
05-24-2010, 07:17 PM
A non-looping option would be to use Replace:
Sub NoZero()
Range("C" & ActiveCell.Row & ":AX" & ActiveCell.Row).Replace _
what:="0", replacement:="", lookat:=xlWhole
End Sub

genracela
05-27-2010, 12:00 AM
Thanks!

It worked!

mdmackillop
05-27-2010, 12:29 AM
I use this to clear selected areas of zero values

Sub delzero()
For Each cel In Intersect(Selection, ActiveSheet.UsedRange)
If cel.Value = 0 Then cel.ClearContents
Next
End Sub



and for Error cells

Sub DelErrors()
On Error Resume Next
If MsgBox("Clear error cells?", vbYesNo) = vbYes Then
Selection.SpecialCells(xlCellTypeFormulas, 16).ClearContents
Selection.SpecialCells(xlCellTypeConstants, 16).ClearContents
End If
End Sub

genracela
05-27-2010, 12:32 AM
Thanks again mdmakiilop!