Need to delete content in cell but keep all formulas

[s7yzrs]

good evening everybody,

I am trying to figure out a way to delete data from cells but to keep the formulas.

This is the macro I am using

"Private Sub BtnNew_Click()

Sheet1.Range("A2:T2,V2,X2,Y2,Z2,AA2,AB2,AC1,AD1,AE1,AC2,AD2,AE2,AF2").Select
Range("AF2").Activate
Selection.SpecialCells(xlCellTypeConstants, 3).Select
Selection.ClearContents
Sheet1.Range("A2") = ""
Sheet1.Range("B2") = ""
Sheet1.Range("C2") = ""
Sheet1.Range("D2") = ""
Sheet1.Range("E2") = ""
Sheet1.Range("F2") = ""
Sheet1.Range("G2") = ""
Sheet1.Range("H2") = ""
Sheet1.Range("J2") = ""
Sheet1.Range("K2") = ""
Sheet1.Range("L2") = ""
Sheet1.Range("M2") = ""
Sheet1.Range("N2") = ""
Sheet1.Range("O2") = ""
Sheet1.Range("P2") = ""
Sheet1.Range("Q2") = ""
Sheet1.Range("R2") = ""
Sheet1.Range("S2") = ""
Sheet1.Range("T2") = ""
Sheet1.Range("V2") = ""

end sub"

The problem is that when the cells contain no data I get an error message.

Any help will be highly appreciated

Attached please find the test file I am working on

Thank you all

[queuesef]

if you're just clearing data, how bout [a2:v2]="" in vba ? PS no animals were hurt in this testing!! ;+ )

[s7yzrs]

HI queuesef, by using that I clear the formulas that are in the cells too...

[cytop]

You cannot clear the cell without clearing the formulas as well but you could edit the formula to only display a result if other conditions are met:

=IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2)

Calculates A2 - B2 if both A2 & B2 contain data otherwise returns a null string.

(Simplistic example ignoring text input instead of numbers)

[s7yzrs]

Hi cytop, you mean that this way I don't have to clear them... sounds safer than what I was looking for... let me test it....

[s7yzrs]

Hi Cytop, It works. The only thing is that when I insert the formula in Z2 and AA2 (calculation of Actual Ground Time) AF2 returns a #VALUE. I have checked the settings and they are ok and I have formatted all the cells as time hh:mm

[cytop]

Probably because of the =TEXT( function is some cells. (Phone at the moment so couldn't look too closely).

There are any number of error checking functions you can use, it's more a case of finding out about them and applying the most relevant. For example, another formula you could use is

=IFERROR(AE2-AD2, "Data error")

Which will simply calculate the referenced cells or display any other value you specify if there's any error, but that's not really what you want here. It's just an example.

You need to validate the results of each cell working back from AF2

[Marc L]

The problem is that when the cells contain no data I get an error message.

So obviously just checking if the first cell is empty like in this VBA demonstration according to your attachment :

Sub Demo1() 
    With FlightDetails.UsedRange.Rows(2) 
        If Not IsEmpty(.Cells(1)) Then .SpecialCells(2).ClearContents 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[s7yzrs]

Hello everybody, sorry for the late reply but I had to work.

It works. Thank you so much all for your help