VBA delete multiple rows by comparing 3 adjacent rows

Question

I am trying to figure out noisy data and delete the whole row of noisy data. There are 79 rows of data in total and about 6-7 noisy data, the data are all integers. To define noisy data, I dim 3 variables: row1,row2,row3, and compare the absolute value of difference between each other, and if Absolute(row1-row2) and Abslute(row2-row3) are bigger than 5 at the same time, I will say row2 is a noisy data and delete the whole row. However, I am having application or object defined error problems. I tried to dim the variables with integer, variant, long, double. None of them works and it is the same problem.

Sub project()
    Dim a As Long, row1 As Long, row2 As Long, row3 As Long
    Dim rKill As Range
    a = 2
    Do
        row1 = Cells(a, 2).Value
        row2 = Cells(a + 1, 2).Value
        row3 = Cells(a + 2, 2).Value
        If Abs(row1 - row2) > 5 And Abs(row2 - row3) > 5 Then
            If rKill Is Nothing Then
                Set rKill = Cells(a + 1, 2)
            Else
                Set rKill = Union(rKill, Cells(a + 1, 2))
            End If
        End If
        a = a + 1
    Loop Until deltat_value1 = 14700
    rKill.EntireRow.Delete
End Sub

The problem is as following:Application-defined or object defined errorand happens at the line: deltat_value3 = Cells(a + 2, 2).Value Then I am thinking it may be because I define too many variables. I changed a little bit to:

Sub project()
    Dim a As Long, row1 As Long, row2 As Long, row3 As Long
    Dim rKill As Range
    a = 2
    Do
        row1 = Cells(a, 2).Value
        row2 = Cells(a + 1, 2).Value
        If Abs(row1 - row2) > 5 And Abs(row2 - row3) > 5 Then
            row3 = Cells(a + 2, 2).Value
            If Abs(row2 - row3) > 5 Then
            If rKill Is Nothing Then
                Set rKill = Cells(a + 1, 2)
            Else
                Set rKill = Union(rKill, Cells(a + 1, 2))
            End If
        End If
        End If
        a = a + 1
    Loop Until deltat_value1 = 14700
    rKill.EntireRow.Delete
End Sub

But it still doesn't work, this time the same error shows for the line:

row2 = Cells(a + 1, 2).Value

having (0 and 6) and (6 and 12) will count the line as noisy and delete it... is there no need to check if the line above and below (0 and 12) is < 10... if you get what i mean ^^; — Dirk Reichel

Dirk Reichel Dirk Reichel · Accepted Answer · 2016-02-09T22:32:29

Wouldn't something like this do exactly what you want?

Sub delMe()
  Dim i As Long, rng As Range
  i = 2 'change to the row you want to start
  While Len(Cells(i + 1, 2).Value)
    If Abs(Cells(i - 1, 2).Value - Cells(i, 2).Value) And Abs(Cells(i + 1, 2).Value - Cells(i, 2).Value) Then
      If rng Is Nothing Then Set rng = Rows(i) Else Set rng = Union(rng, Rows(i))
    End If
    i = i + 1
  Wend
  rng.Delete
End Sub

Still: I don't know how deltat_value1 will change inside the loop? Wouldn't run your loop till it hits the end of the sheet?

VBA delete multiple rows by comparing 3 adjacent rows

1 Answers