IF Function (Excel 2003)

Topic

New Reply

I hope someone can help me with this excel IF statement.

I have an excel sheet that has temperature readings entered automatically every 10 minutes. I would like the entire row to be entered if the temperature is different from the row above. If the temperature is the same, delete or leave out the entire row . The spreadsheet has 4 columns entering time, date, depth and temperature. i.e. enter data only if the temperature changes, otherwise delete the entire row.

Thank you…Susan

Reply | Quote

Replies

I don’t think you can do this using formulas, you’d need to use VBA code. But if the temperature readings are entered automatically, when should the code run?

Reply | Quote

Try placing this in the Worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then 'Assumes Temperature is in coliumn 4 (D)
        Application.OnTime Now + TimeValue("00:00:05"), "Compare_Temps"
    End If
End Sub

And this in a standard module

Option Explicit
Public Sub Compare_Temps()
Dim ROI As Long

' Set COI to the Temperature column
Const COI = 4

    Application.EnableEvents = False
    ROI = Range(Cells(65536, COI).Address).End(xlUp).Row
    If Cells(ROI, COI) = Cells(ROI - 1, COI) Then
        Range("A" & ROI).EntireRow.Delete
    End If
    Application.EnableEvents = True
End Sub

The delay has been set to 5 seconds to allow the data entry to be completed irrespective of the entry order.

HTH

Reply | Quote