CODEWITHSUNDEEP
vbaexcel
sagar
sagar

Reputation: 397

How to make Excel VBA to ignore formula cells with if condition

I want to use if condition to perform ClearContents task. Please find my below code for the same. I written below 2 code's, but both are unsuccessful.

  1. First Try

    t = 1
Do While Cells(t, 1) <> ""
    If Cells(t, 1) <> "=" Then
        Cells(t, 1).ClearContents
    End If
    t = t + 1
Loop

  2. Second Try

    t = 1
Do While Cells(t, 1) <> ""
    If Cells(t, 1) <> Formula Then
        Cells(t, 1).ClearContents
    End If
    t = t + 1
Loop

Basically, I don't want to delete the cells contain where formulas are available but I want to delete the other data.

Upvotes: 5

Views: 4443

Answers (2)

Vityata
Vityata

Reputation: 43595

Write something like this:

If Not Cells(t,1).HasFormula Then

End if

It would work. Or try like this:

Sub TestMe()

    If Not Cells(1, 1).HasFormula Then
        Debug.Print "No Formula"
    Else
        Debug.Print "Has Formula"
    End If

End Sub

Here is more info about the .HasFormula property:

https://msdn.microsoft.com/en-us/library/office/ff837123.aspx

Upvotes: 2

vacip
vacip

Reputation: 5426

As an alternative, there is a method to select all the cells containing constant (non-formula) values.

Cells.SpecialCells(xlCellTypeConstants, 23).ClearContents

Faster than iterating all the cells.

Or to do this only on a specific range or the current selection, replace Cells with Range("A1:A100") or Selection.

(In Excel, you can find this under Home -> Editing -> Find & Select -> Go to Special. Here you can have Excel automatically select only the Constants or the Formulas inside the existing selection.)

Upvotes: 3

Related Questions