有人能帮我吗?我甚至不知道怎么开始..。我想要创建一个宏,删除重复项(基于A列),并保留具有最新日期的行(P列)。如果所有副本在P列中都没有日期,只需保留一个,然后删除其他副本。

表中的数据以第5行开头(不像图片中的第4行那样,对不起)。在过去,我知道当表不以第1行或第2行开始时,我在通过宏删除重复项时遇到了问题。
该表通常有大约15列和大约10.000行。
有些行在P列中有日期,而有些行没有。因此宏应该检查是否有重复项(A列),如果有,请检查列P中是否有日期。如果有更多的重复日期,则宏应该删除所有重复项,但保留最近的副本。
我迄今使用/编辑的“守则”:
Sub DelDubs_Date()
Dim Rng As Range
Dim LastRow As Long
Dim i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("A5:P" & LastRow)
With Rng
.Sort key1:=Range("A5"), order1:=xlAscending, key2:=Range("P5"), order2:=xlDescending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End With
For i = LastRow To 2 Step -1
If WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(i, "A")), Cells(i, "A")) > 1 Then
Rows(i).Delete
End If
Next i
Application.ScreenUpdating = True
End Sub问题:它保留了第一排,而不是最近的日期.
TL;DR:在A中检查副本,然后在P中检查日期,然后删除所有重复,但保留最新的。如果没有日期,请删除所有副本并保留一个.
发布于 2016-08-18 13:54:50
由于我在2013年的office中遇到了这里和这里 -faced描述的删除重复错误,所以这些线程与2010年有关,我不希望他们在2016年修复它--我从来不依赖这个函数,相反,我编写了以下代码:
Sub TryMe()
Call RealRemoveDuplicates("MySheet", Range("A1:C5"))
End Sub
Sub RealRemoveDuplicates(InSheet As String, InRange As Range)
Call CreateSheets("DummyDuplicate")
Sheets(InSheet).Range(InRange.Address(False, False)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"A1"), Unique:=True
Sheets(InSheet).Range(InRange.Address(False, False)).Clear
ActiveSheet.UsedRange.Copy Destination:=Sheets(InSheet).Range(InRange.Address(1))
Sheets("DummyDuplicate").Delete
End Sub
Sub CreateSheets(NameSheet As String, Optional Looked_Workbook As Workbook)
Dim SheetExists As Worksheet
If Looked_Workbook Is Nothing Then Set Looked_Workbook = ThisWorkbook '1. If Looked_Workbook Is Nothing
On Error GoTo ExpectedErr01CreateSheets
Set SheetExists = Looked_Workbook.Worksheets(NameSheet)
SheetExists.Delete
If Err.Number <> 0 Then '2. If Err.Number <> 0
ExpectedErr01CreateSheets: 'this means sheet didn't existed so, we are going to create it
End If '2. If Err.Number <> 0
With Looked_Workbook
.Sheets.Add After:=.Sheets(.Sheets.Count)
ActiveSheet.Name = NameSheet
End With
End Sub发布于 2016-08-18 14:05:44
将日期从文本转换为日期,您可以记录其中的宏:
Conf. Date从最新到老排序Remove Duplicates >取消选中除REF列之外的所有REF列排序我认为这将更容易,更灵活的PivotTable或PowerPivot。
发布于 2016-08-18 18:49:31
通常情况下,我会把所有这些都扔到一个子中,但是你似乎喜欢@John的解决方案。我测试过一次,如果我漏掉了什么,就让我知道。
Option Explicit
Dim wbk As Workbook
Dim ws As Worksheet
Dim lRow As Long
Sub CallSubs()
Call FormatDates
Call SortSmall
Call RemoveDups
End Sub
Sub FormatDates()
Set wbk = Workbooks("Book1.xlsm")
Set ws = wbk.Worksheets("Sheet1")
With ws
'Find last row
lRow = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'This will only work if Columns B through O have data
'Turn on Autofilter
If .AutoFilterMode = False Then
.Cells(3, 1).AutoFilter
End If
.Range("P4:P" & lRow).Replace What:=".", Replacement:="/", LookAt:=xlPart, MatchCase:=False
.Range("P4:P" & lRow).NumberFormat = "dd/mm/yyyy;@"
End With
End Sub
Sub SortSmall()
Set wbk = Workbooks("Book1.xlsm")
Set ws = wbk.Worksheets("Sheet1")
With ws
lRow = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'I used the macro recorder for this and cleaned it up let me know if there is a better way
'Sort Dates Z To A
.AutoFilter.Sort.SortFields.Clear
.AutoFilter.Sort.SortFields.add Key:=.Range("P3:P" & lRow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With .AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub
Sub RemoveDups()
Set wbk = Workbooks("Book1.xlsm")
Set ws = wbk.Worksheets("Sheet1")
With ws
lRow = .Cells.Find(What:="*", _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
.Range("A3:P" & lRow).RemoveDuplicates Columns:=1, Header:=xlYes
End With
End Subhttps://stackoverflow.com/questions/39017588
复制相似问题