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?
Keep those rows of a material having highest Qty-Plant Wise
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Keep those rows of a material having highest Qty-Plant Wise
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Keep those rows of a material having highest Qty-Plant W
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: Keep those rows of a material having highest Qty-Plant W
Thanks a lot Hans.Macro is working perfect that was desired.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA