Friends i'm Creating Macro for "Locating Duplicate values in a Column n new date(Max date in Corresponding column)....i'm not Getting Logic for that pls Help me in the Coding...
i Data is like this
x y Result
3 8/6/2013 8/6/2013
3 8/5/2013
3 8/4/2013
2 9/3/2013 9/2/2013
2 9/2/2013
2 9/1/2013
1 9/6/2013 9/6/2013
1 9/3/2013
1 9/2/2013
i tried this by sorting..its Working Better for Small Data..but not for Large... Even i Want to Reduce the code Length..
Here is My code...
Sub Macro2()
Range("a1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "fstcol"
Range("b1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "seccol"
Range("a1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "myrange"
Range("a1").Select
Range("myrange").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("fstcol") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("seccol") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("myrange")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("a1").End(xlToRight).Offset(0, 1).Value = "Flag"
Range("a1").End(xlToRight).Offset(1, 0).Select
ActiveCell.Value = 1
Do While ActiveCell.Offset(0, -2).Value <> "" And ActiveCell.Offset(1, -2).Value <> ""
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, -2).Value = ActiveCell.Offset(1, -2).Value Then
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
End If
Loop
Range("a1").End(xlToRight).Offset(1, 0).Select
Do While ActiveCell.Offset(0, -2).Value <> ""
If ActiveCell.Value = 1 Then
ActiveCell.Value = ActiveCell.Offset(0, -1).Value
Else: ActiveCell.Value = ""
End If
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
Range("a1").Select
End Sub
Bookmarks