VBA to Filter a value & Clear cells.

[Revathi Kannan]

I have a column which named as "Logic" wherein i need to filter the below 2 values from the said column and clear only the contents of the cell.

> 0
>00-01-1900 0:00:00

The major challenge is, i am not able to clear the cells of incorrect date format from the above point. Please assist.

 

[Yongle]

Try this
- assumes values are in column D starting at D2
- sets format in found cells to general

Sub ClearPositiveValues()
    Dim ws As Worksheet, cel As Range, eRng As Range, myStr As String
    Set ws = ActiveSheet
       
    For Each cel In ws.Range("[COLOR=#ff0000]D2[/COLOR]", ws.Range("[COLOR=#ff0000]D[/COLOR]" & Rows.Count).End(xlUp))    [I][COLOR=#006400]'amend to match your data[/COLOR][/I]
        If cel > 0 Then
            If myStr = "" Then myStr = cel.Address Else myStr = myStr & "," & cel.Address
        End If
    Next
    If Not myStr = vbNullString Then
        With ws.Range(myStr)
            .ClearContents
            .NumberFormat = "General"                                       [COLOR=#006400][I]'format as required[/I][/COLOR]
        End With
    End If
End Sub

 

[Revathi Kannan]

But it is showing an object defined error.

 

[Yongle]

I tested the code before posting - it worked
I tested the code again, even on an empty sheet - it runs without failing
I think you have amended something incorrectly

Which cell contains header "Logic"?
What is the name of the sheet?

thanks