@snb
if Sheets("before") is not active sheet, it throws "run time error 13, type mismatch."
any comments as to why?
and consolidation of subprocedures can be as follows:
Sub M_snb()
With Sheets("before")
.Activate
sp = .Cells(1).CurrentRegion
End With
sn = Filter([Transpose(If(A1:A15000="","~",If(CountIf($A$1:$A$15000,A1:A15000)=1,Row(A1:A15000),"~")))], "~", False)
sq = Filter([Transpose(If(A1:A15000="","~",If(CountIf($A$1:$A$15000,A1:A15000)>1,Row(A1:A15000),"~")))], "~", False)
With Sheets("before")
.Cells(1).CurrentRegion.ClearContents
.Cells(1).Resize(UBound(sn) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sn), Array(1, 2, 3, 4))
End With
Sheets("duplicate records").Cells(1).Resize(UBound(sq) + 1, UBound(sp, 2)) = Application.Index(sp, Application.Transpose(sq), Array(1, 2, 3, 4))
End Sub