CODEWITHSUNDEEP
excelvba
Neo Yi Peng
Neo Yi Peng

Reputation: 33

How to clear cell if formula returns blank value?

I have a formula like this:

 if(A1="","",1)

How do I do a quick search to remove the formula if it is ""? I tried searching for blanks, but because there is a formula, it does not return a blank.

Upvotes: 1

Views: 10626

Answers (1)

hnk
hnk

Reputation: 2214

You need to use the following code

Sub ClearCell()
    Dim Rng As Range
    Set Rng = ActiveSheet.Range("A1")

    Dim i As Long
    For i = 1 To 10
        If Rng.Cells(i,1) = "" Then
           Rng.Cells(i,1).ClearContents
        End If
    Next i
End Sub

That will leave the cell truly EMPTY and not with a zero-character "" string.

If you are looking to clear arbitrary length whitespace in cells, use

If Len(Trim(Rng.Cells(i,1))) = 0 Then
' .....

This condition will be true for any number of spaces or no spaces (empty string). That way (almost) anything which looks blank will get cleared.

Upvotes: 2

Related Questions