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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.