Clear Contents Value, Move to Next Cell in Column

[capo4410]

Hello,

I need some help on a macro I am attempting to create. I am completely new to VBA so am struggling a bit with the proper macro to make my desired outcome work. I created a formula in Excel which adds 0s to cells when a condition is met. Ultimately, I need that cell clear of the 0 in order to upload to a website or else an error will occur.

The macro shown below removes the 0 but only in the cell selection. I want the macro to run subsequently in cells down 3 columns, AN, AO, and AP. Any help is much appreciated! Thank you.


Sub DelZeros()
Dim c As Range
For Each c In Selection
If c.Value = 0 Then c.ClearContents
Next c
End Sub

 

[mumps]

This macro assumes you have headers in row 1 and your data starts in row 2:

Sub DelZeros()
    Application.ScreenUpdating = False
    Dim LastRow As Long, c As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each c In Range("AN2:AP" & LastRow)
        If c.Value = 0 Then c.ClearContents
    Next c
    Application.ScreenUpdating = True
End Sub

 

[capo4410]

Wow! Thank you so much, mumps and thanks for such a quick response! This worked perfectly! Could I bother you for another one I'm attempting for the same project? I can add it here, or create a new thread...

 

[Rick Rothstein]

Wow! Thank you so much, mumps and thanks for such a quick response! This worked perfectly! Could I bother you for another one I'm attempting for the same project? I can add it here, or create a new thread...

Here is another (slightly different) macro that should also work for you...

Sub DelZeros()
  With Intersect(Columns("AN:AP"), ActiveSheet.UsedRange)
    .Value = Evaluate("IF(" & .Address & "<>0," & .Address & ","""")")
  End With
End Sub

 

[capo4410]

Thank you, Rick!