Compare value in current row and the next row

[Irene94]

I'm a novice in Excel VBA. Currently, I have imported a bunch of data into Excel. Is it possible to compare the value of current row and the next row? If there is same value occurred in column A, compare column B and the row with larger value in column B will be selected and another row will be deleted. I need some help or suggestion! Thanks!
Example: row A11: 397 B11:-57.6 will be deleted. The data is in this form:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> A B
11 397 -57.6
12 397 -57.3
13 270 -55

</code>

 

[frank_AL]

Irene,

This code determines the last row in Column A then evaluates each row with the next row starting from the last row, evaluates if the values in Column A are equal, then determines which value in Column B is greater and deletes that row. If the values in Column B are equal this code does nothing with either row.

Let me know if this accomplishes what you are looking for.

Sub CompareValues()
Dim lr As Long
Dim i As Long, x As Long


lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 Step -1
    x = i - 1
    If Cells(i, 1).Value = Cells(x, 1).Value Then
        If Cells(i, 2).Value > Cells(x, 2).Value Then
            Rows(i).Delete
'            i = i - 1
        ElseIf Cells(x, 2).Value > Cells(i, 2).Value Then
            Rows(x).Delete
'            i = i - 1
        End If
    End If
Next i
End Sub

 

[Irene94]

Irene,

This code determines the last row in Column A then evaluates each row with the next row starting from the last row, evaluates if the values in Column A are equal, then determines which value in Column B is greater and deletes that row. If the values in Column B are equal this code does nothing with either row.

Let me know if this accomplishes what you are looking for.

Sub CompareValues()
Dim lr As Long
Dim i As Long, x As Long


lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 Step -1
    x = i - 1
    If Cells(i, 1).Value = Cells(x, 1).Value Then
        If Cells(i, 2).Value > Cells(x, 2).Value Then
            Rows(i).Delete
'            i = i - 1
        ElseIf Cells(x, 2).Value > Cells(i, 2).Value Then
            Rows(x).Delete
'            i = i - 1
        End If
    End If
Next i
End Sub

Hi frank! The code works great! This is exactly what I am looking for! Thank you very much!

 

[Irene94]

Irene,

This code determines the last row in Column A then evaluates each row with the next row starting from the last row, evaluates if the values in Column A are equal, then determines which value in Column B is greater and deletes that row. If the values in Column B are equal this code does nothing with either row.

Let me know if this accomplishes what you are looking for.

Sub CompareValues()
Dim lr As Long
Dim i As Long, x As Long


lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = lr To 2 Step -1
    x = i - 1
    If Cells(i, 1).Value = Cells(x, 1).Value Then
        If Cells(i, 2).Value > Cells(x, 2).Value Then
            Rows(i).Delete
'            i = i - 1
        ElseIf Cells(x, 2).Value > Cells(i, 2).Value Then
            Rows(x).Delete
'            i = i - 1
        End If
    End If
Next i
End Sub

Hi Frank! May I ask for some additional help? I have 2 sets of data and the data form is as stated in my question, and the data is filtered with the code provided by u successfully!

[H.Freq]
data 1

[V.Freq]
data 2

There will be double spacing between data 1 and title 2, how can I arrange the data in ascending order part by part? Because I have search through the network, the code will mix up the value of both part.