VBA in Excel: How to keep relevant rows only? [Resolved by NeedSomeAnswers]

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

Hi John,

The following code should work, but first to make it work you need to create a Range.

To do this you simply highlight the cells with the "value 1" , "value 2" (this should be only 1 column, i.e B1 TO B20) data in them and go to :

Insert
-- Name
-- Define

and name the range "colb"

VB Code:

Private Sub GetHighestValues()
 
Dim strValues() As String
Dim lngAmounts() As Long
Dim n, i As Integer
Dim max As Long
 
n = 1
i = 1
 
For Each cell In Range("colb")
    If n > 1 Then
        If cell.Value <> strValues(i - 1) Then
            ReDim Preserve strValues(i)
            ReDim Preserve lngAmounts(i)
            strValues(i) = cell.Value
            lngAmounts(i) = cell.Offset(0, 1).Value
            i = i + 1
        Else
            If cell.Offset(0, 1).Value > lngAmounts(i - 1) Then
                lngAmounts(i - 1) = cell.Offset(0, 1).Value
            End If
        End If
    Else
        ReDim Preserve strValues(i)
        ReDim Preserve lngAmounts(i)
        strValues(i) = cell.Value
        lngAmounts(i) = cell.Offset(0, 1).Value
        i = i + 1
    End If
    n = n + 1
Next cell
 
max = UBound(strValues)
n = 1
 
For Each cell In Range("D1", "D" & CStr(max))
    cell.Value = strValues(n)
    cell.Offset(0, 1).Value = lngAmounts(n)
    n = n + 1
Next
 
End Sub

The results are placed by the second "FOR" loop, in column D, but you can obviously change that if you want !

If you have any problems with the code, post back !

Hi,

thanks a lot, it's just perfect solution! As a newbie here, I am really amazed that you (unknown) took the time to sit down and think of this issue. Since

I speak in VB for the last three days only, I still don't understand how could you get it, but I am sure I will get it (one day...)

Thanks again and have a nice day!

John

Hi John, no problem !

i have recieved help quite a few times from this forum, and so i like to help back when i can.

Richard

Hello,
I also have a Excel problem and im a total newbie with VB. Can someone explain this code to me? Its suppose to delete rows with "0" but when its ran its skips a block of rows on my sheet. Also can you explain the Range(selection) row.
Thanks,
:)

Delete all rows with no account field
Application.WindowState = xlMinimized
Sheets("MAIN").Select
Workbooks.Open Filename:=Range("A200")
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete