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