Keep those rows of a material having highest Qty-Plant Wise

[PRADEEPB270]

I have a file and want to solution with the help of VBA codes.In this file,a sheet have some datas,and require the report on this sheet.
Only keep that rows of a 'Material' who have highest Qty of a 'Material' and it should be 'Plant' wise.It may be possible that a 'Material' appear twice or more than twice but 'plant' will also repeat.Look up the each 'Material' and 'Plant' simultaneously.

For more clarification,please refer attach file.First sheet is 'Datas' and last sheet is 'Display-Result' i.e. requirement.

Can it be possible through VBA Codes?

[HansV]

Here you go:

Code: Select all

Sub KeepHighestQty()
    Const FirstRow = 4
    Const PlantCol = "B"
    Const MaterialCol = "C"
    Const QtyCol = "F"
    Dim LastRow As Long
    Dim r As Long
    Dim DeleteRange As Range
    Range(PlantCol & FirstRow).CurrentRegion.Sort _
        Key1:=Range(PlantCol & FirstRow), _
        Key2:=Range(MaterialCol & FirstRow), _
        Key3:=Range(QtyCol & FirstRow), Header:=xlYes
    LastRow = Range(PlantCol & Rows.Count).End(xlUp).Row
    For r = FirstRow + 1 To LastRow
        If Range(PlantCol & r).Value = Range(PlantCol & (r + 1)).Value And _
                Range(MaterialCol & r).Value = Range(MaterialCol & (r + 1)).Value Then
            If DeleteRange Is Nothing Then
                Set DeleteRange = Range(PlantCol & r)
            Else
                Set DeleteRange = Union(Range(PlantCol & r), DeleteRange)
            End If
        End If
    Next r
    If Not DeleteRange Is Nothing Then
        DeleteRange.EntireRow.Delete
    End If
End Sub

[PRADEEPB270]

Thanks a lot Hans.Macro is working perfect that was desired.