Need a MACRO to Clear Contents of a Cell

0

I have a 10,000+ row worksheet that I need to clear only the cells that have a zero in it.

I am doing this manually; however, thought there might be a MACRO I can create in EXCEL 2016 in order to do this expeditiously.

Any help would be appreciated.

Answer
Discuss

Answers

0

This code will do exactly what you asked for. Be sure you have a backup of the data on the sheet you try it on !!!!

Sub ClearCellsWithZeroValue()
    Dim Cell As Range
    Dim R As Long
    
    Application.ScreenUpdating = False
        For Each Cell In ActiveSheet.UsedRange
            With Cell
                If Val(.Value) = 0 Then
                    If .MergeCells Then
                        .MergeArea.ClearContents
                    Else
                        .ClearContents
                    End If
                End If
                If .Row <> R Then
                    R = .Row
                    If R Mod 100 = 0 Then _
                       Application.StatusBar = "Examining row " & R
                End If
            End With
        Next Cell
    Application.ScreenUpdating = True
    MsgBox R & " rows were examined and" & vbCr & _
           "zero values removed.", vbInformation, "Action report"
    Application.StatusBar = ""
End Sub

Unfortunately, your question isn't a good one. Therefore you don't get a really good answer. A better question would have specified a column where you want zero values removed. It would have mentioned if that column has a caption at the top. If it was "very good" it would have said whether there is text somewhere which should be spared (minding that any text evaluates to zero).

The above code removes anything, anywhere on the currently active worksheet, that evaluates to zero. This indiscriminate action, carried out on a sheet with 10,000+ rows and an unknown number of columns will take a little time. There will be a progress indicator in the status bar (bottom left corner of your screen) and a message box when the deed is done.

Discuss
0

How about a Find & Replace All where you search for zero and replace the cell with nothing (just leave the replace with field blank).

Just hit Ctrl + F to get to the correct pop-up window and move to the Replace tab and go from there.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login