Clear Contents

[TexanJohn]

I have a column of data with numeric values. Anything greater than 0 is valid, but how can I easily clear the contents of the cells with '0' as a value?

I have the same problem with a column of data with text. Anything with a value greater than "" is valid, but many cells have a value of "" (blank) that I want to clear. I have been trying different formulas, etc.

My reason for wanting to clear these values (either 0 in number column or blank in text column) is so that the finding the next valid value down the column works properly.

Any help is appreciated.

Thanks.

 

[crazydragon84]

find and replace all 0 with ""?

alternatively autofilter for value = 0 and clear contents

 

[Qwest336]

Sub ClearZeros()
With Sheets("Sheet1") ' Change to your sheet name
LR = .Cells(Rows.Count, "A").End(xlUp).Row 'Change "A" to whichever column
For i = LR To 2 Step -1 'Skips header row
If .Cells(i, "A").Value = 0 Then
.Cells(i, "A").Clear
End If
Next i
End With
End Sub

 

[TexanJohn]

Thanks for the suggestions.

I used something like this for clearing the 0's

Sub clrfrml()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("B2:B9527")
With c
If .Value = 0 Then .ClearContents
End With
Next c
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[crazydragon84]

Sub delete_Zero()
    Columns("B:B").Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Way faster than looping through each range.

 

[Qwest336]

Thanks for the suggestions.

I used something like this for clearing the 0's

Sub clrfrml()
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim c As Range
For Each c In Range("B2:B9527")
With c
If .Value = 0 Then .ClearContents
End With
Next c
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Sorry for the lack of code quotes, btw.

That works fine. A couple of things though:

1). I think my method will probably be slightly faster. You can certainly test that out and it may be negligible.
2). Make sure to incorporate some way to address the changing of shape in your data. My method will determine the last row and work until it's complete. Your method will require you to change the last row

("B2:B9527")

each time there is data added or removed from your dataset.

Good job!