VBA in Excel: How to keep relevant rows only ?

[jl88]

Hi everybody!

Do you know, guys, about some easy way how to tell VBA in Excel to go thourgh all cells in worksheet and keep those ones with highest number of each 'value' in list similar to the following?

value1 6
value1 3
value1 5
value1 7
value2 5
value2 3
value3 5
value4 2
value4 6

Basically, is it possible to get via some universal VBA from the list above this selection below?

value1 7
value2 5
value3 5
value4 6

Any ideas, please feel free to post!

Regards,
John

[MichaelNattfalk]

Hi.

I don't know if it is the best way to do it, but atleast it works.
What you need in the Excel book is 2 sheets, "Sheet1" and "Sheet2".
The next thing you need is to have the data in the first column sorted ascending.

Try the code below ... (I've tried it myself and it works)

Sub MyFunction()
    Dim iRow        As Integer
    Dim iRow2       As Integer
    Dim iCol1Value  As Integer
    Dim iCol2Value  As Integer

    With ThisWorkbook.Sheets("Sheet1")
        iRow = 1
        iRow2 = 1
        iCol1Value = .Cells(iRow, 1)

        Do While (.Cells(iRow, 1) <> "")
            iCol2Value = 0
            Do While (.Cells(iRow, 1) = iCol1Value)
                If (.Cells(iRow, 2) > iCol2Value) Then _
                    iCol2Value = .Cells(iRow, 2)
                iRow = iRow + 1
            Loop
            ThisWorkbook.Sheets("Sheet2").Cells(iRow2, 1) = iCol1Value
            ThisWorkbook.Sheets("Sheet2").Cells(iRow2, 2) = iCol2Value
            iRow2 = iRow2 + 1
            iCol1Value = .Cells(iRow, 1)
        Loop
    End With
End Sub

Regards,
Michael