ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Excel2003 remove duplicates¬Û®e©Ê°ÝÃD

[µo°Ý] Excel2003 remove duplicates¬Û®e©Ê°ÝÃD

¦U¦ì¦n
¥Ø«e¤u§@¥æ±µ®É¡Aµo²{Â÷¾¦P¨Æ¦æ±¡¨Ï¥ÎVBA¬ö¿ý¡A¶i¦Ó¶}©lºN¯Á
¦]­Ó¤H¹q¸£ª©¥»»Ý­n§âoffice2013ªºVBA§ï¦¨office2003ªºª©¥»

¦b¦¬¨ú¦æ±¡®É·|¨C¤p®É§R°£­«½Æªº¸ê®Æ ¡A¦¹¤º®e¼g¦b¼Ò²Õ¤¤¡A°õ¦æ®É¦b«ü©wªº32­Ó¤u§@ªí§R°£
¦b§ï¼g®É¹J¨ìremove duplicates¬Û®e©Êªº°ÝÃD¡A
¦³°Ñ¦Òª©¤Wªº¤å³¹¹Á¸Õ§ï¼g¦¨2003ªºª©¥»¦b¤u§@ªí¤º¥i¥H¥¿±`°õ¦æ¡A¦ý¦b¼Ò²Õ¤¤¦³®É·|¸õ¥X¿ù»~
¦A«×°Ñ¦Òºô¸ôªº¸ê®Æ«á §ï¼g¦¨¤U­±ªº¤º®e§Y¥i¦b¼Ò²Õ¤¤¥¿±`°õ¦æ
¦ý°õ¦æ®Éµo²{°£¤F®Ä²vÅÜ®t¥~¡A¸ê®Æ¶qÃe¤j®ÉÁÙ¥i¯à·|·í¾÷QQ

·Q½Ð°Ý¤j®a¹ï©óRemoveDuplicates§ï¼g¬°2003ª©¥i¨Ï¥Îªºª©¥»®É¡A¬O§_¦³¸û¦nªº¼gªk©O?
ÁÂÁ¤j®a!!


­ì©loffice2013¥Îªk¬°
------------------------------------------------------------
Sub delete()

For i = 2 To 33

Sheet(i).Range("B4:AG65536").RemoveDuplicates Columns:=Array(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33), Header:=xlNO

Next i
End Sub
------------------------------------------------------------

¥Ø«e§Ú°Ñ¦Òºô¸ô¸ê®Æ§ï¼goffice2003¬°
------------------------------------
Sub delete()
For i = 2 To 33

Dim r As Range, Rng As Range

Sheets(i).Select

With Sheets(i).UsedRange
.Columns("B:AG").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Dim g%
Application.ScreenUpdating = False
For g = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1 To ActiveSheet.UsedRange.Row Step -1
If Rows(g).Hidden = True Then
Rows(g).EntireRow.delete
End If
Next
Application.ScreenUpdating = True

End With
Next i

End Sub
------------------------------------------------------------

¦^´_ 1# sunshine010


    ¨ä¹êoffice³o­Ó­°µ¥¯Å°ÝÃD¤£¤Ó¥i¯à·|°µªº¨Æ...¬Û«H­n­°µ¥¦³¯S®í¦]¯À§a!
¦Ó§A«üªºremove duplicates¦b2003ª©¥»¦¹¥\¯à¤£¦s¦b¡A
remove duplicates¬O±q2007ª©¥»¤~¶}©l¦³ªº¡A
¤u¨ã¦ì¸m¦b[¸ê®Æ]¡÷[¸ê®Æ¤u¨ã]¡C
(¸Õ¸Õ¿ý»s¥¨¶°´Nª¾¹D¤F)

±q§Aªºµ{¦¡½X¤¤¬Ý°_¨Ó¬O­n¥H2003ª©¶}©l°õ¦æ32­Ó¬¡­¶Ã¯°µ­«Âиê®Æ§R°£¡A¹ï§a?
¨º»ò¥i¥H¥ÎCreateObject("Scripting.Dictionary")«Ø¥ß¦r¨å¤è¦¡´N¥i¥H°µ¨ì¤F¡C
(·j¯Á¥i¥H§ä¨ì«Ü¦h¬ÛÃö°Ñ¦Ò¸ê®Æ¡A¦Ó¥B³£¬O¶Wª©´£¨Ñªº)
¦pªGÁÙ¼g¤£¥X¨Ó¡A¥i¥H¤W¶Ç¸ê®Æ¨Ã»¡©ú­ì¦]¡A¦A¨Ó¨ó³B¸Ñ¨M°ÝÃD¡C

´£¿ô:¦pªG¸ê®Æ¬O¥u­n¨C­ÓSheet¤£­«ÂнЦA¤U­ÓSheet«eNothing¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 2# faye59


±z¦n ÁÂÁ±zªº¦^ÂÐ §Ú¥»¨­¤£·|¼gVBA¡A¥D­n¬O¦]¬°§Ú¹q¸£¥u¦³¶REXCEL2003 ©Ò¥H¥u¯à·Q¿ìªkÅýVBA¥i¥H¦b2003ª©°õ¦æ

¤]¦³µo²{ remove duplicates¬O2007¦~¤~¦³ªº¥\¯à¡A¦]¦¹¤~¯S§O­W´oQQ

ÁÂÁ±z´£¨Ñ«Øij¬°CreateObject("Scripting.Dictionary")

³o­Ó¥Îªk§Ú¦bPO¤å«e¨S¦³¬d¨ì¡A§Ú¤]¨Ó¬ã¨s¬Ý¬Ý ¦pªG¼g¤£¥X¨Ó¦A¤W¨Ó½Ð±Ð¦U¦ì ÁÂÁ¡I

TOP

¦^´_ 3# sunshine010
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng(1 To 2), Sh As Worksheet, i As Integer, E As Variant
  4.     Set Sh = Workbooks.Add(1).Sheets(1)  '©Ò·s¼W¬¡­¶Ã¯ªº²Ä¤@±i¤u§@ªí
  5.     For i = 2 To 33
  6.         Set Rng(1) = Sheets(i).Range("B4:AG65536")
  7.         Set Rng(2) = Sh.Range(Rng(1).Address)
  8.          With Rng(2)
  9.             .Clear
  10.             .Value = Rng(1).Value  '½Æ»sRng(1)
  11.         End With
  12.         For Each E In Array(2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33)
  13.             Rng(1).Columns(E).AdvancedFilter xlFilterCopy, "", Rng(2).Cells(E), True
  14.             '¨C¤@Ä檺 ¶i¶¥¿z¿ï ¤£­«½Æªº­È ¨ì Rng(2)ªº¨C¤@Äæ
  15.         Next
  16.         Rng(1).Value = Rng(2).Value
  17.     Next
  18.     Sh.Parent.Close False   'Ãö³¬·s¼W¬¡­¶Ã¯
  19. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE


    GBKEE¤j «D±`ÁÂÁ±zªºÀ°¦£  §Ú¤µ¤Ñ¦­¤W¸Õ¤F¤@¤U

µo²{¥L·|¦bSet Rng(1) = Sheets(i).Range("B4:AG65536")¸õ¥X¿ù»~

°T®§¬°"°}¦C¯Á¤Þ¶W¥X½d³ò" ¤£¬O«Ü²M·¡³o­Ó¿ù»~ªº­ì¦]  

·Q½Ð°Ý±z¬O§_ª¾¹D¬°¤°»ò½d³ò¨S¶W¹L¤u§@ªí­­¨î¤´·|¿ù»~¶Ü

TOP

¦^´_ 5# sunshine010


¬O¤£¬OÅã¥Ü"°õ¦æ¶¥¬q¿ù»~'9':°}¦C¯Á¤Þ¶W¥X½d³ò" ?
À˵ø§AªºMicrosoft Excelª«¥ó¸ê®Æ§¨¤U¦³´X­ÓSheet¡A
¦]¬°µ{§Ç¤¤For i = 2 To 33³o¬O©T©w­È¡A
33¨Ã¤£¬O¥ÎSheetªºÁ`©M¡C

¦pªGÁÙ¬O¦³°ÝÃD½ÐªþÀÉ¡A¹ê»Ú´ú¸Õ¤~ª¾¹D°ÝÃD¦b­þ¡C
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 6# faye59


    faye59¤j ±z¦n

Microsoft Excelª«¥ó¸ê®Æ§¨¤U¦³33­ÓSheet
§Ú»Ý­n¥L¶]¤u§@ªí2¨ì¤u§@ªí33
Å¥¤£¤ÓÀ´±zªº·N«äQQ

¨M©w§âÀɮשñ¤W¨Ó ¦A³Â·Ð±z¬d¬Ý ÁÂÁ±z¡I

PS. °õ¦æªºµ{¦¡ ©ñ¦b"¼Ò²Õ"ªºDDE¸Ì­±ªºdelete()

DDE_main - §ó§ï.zip (70.97 KB)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2018-9-10 13:18 ½s¿è

¦^´_ 7# sunshine010

1) ¥h­«ÂÐ, À³¸Ó¥u°w¹ïBÄ檺[®É¶¡]³B²z, ­Y³vÄæ³B²z, ¨º³Ì«áªº¸ê®Æ¤]¤£¥¿½T
2) VBA¥H[¤½¦¡­«ºâ]IJµo, ¦P®É¶¡°O¿ýªº¸ê®Æ¤@©w·|­«ÂЫܦhµ§, ³o¬OWorksheet_Calculateªº¹ú¯f
3) §R°£­«ÂЦP®É¤SIJµoµ{¦¡, ¤]·|³y¦¨¤£¥i¹w®Æªº¿ù»~

®Ú¥»¸Ñ¨M¤èªk:
³]¤@¼È¦s¦C, ¦s©ñ¤W¤@µ§°O¿ýªº¤º®e, ¦A¥Î¤@­Ó¤½¦¡§PÂ_¸ê®Æ¬O§_¦³ÅÜ°Ê,
­YµLÅÜ°Ê, §Y¨Ï¤½¦¡­«ºâ¤]¤£°O¿ý, ³o¼Ë®Ú¥»¤£¶·¦A¥h°õ¦æ§R°£­«ÂÐ

³oDDE«D§Ú©Òªø, À°¤£¤W¦£~~

TOP

¦^´_ 7# sunshine010


    ¦p­ã¤j©Ò¨¥
¬Ý¹L©Ò¦³µ{§Ç«á§Ú©Ò²q´úªº°ÝÃD¤]¬O¦p¦¹¡A
Dynamic Data Exchange(²ºÙ:DDE)ªº¬[ºc§Ú¤]¤£¬O«ÜÀ´¡A
§Ú¬O¥ÎApplication Programming Interface(²ºÙ:API)¤è¦¡»Î±µ¸ê®Æ¡C

¨S±µÄ²¹LªÑ²¼...¬Ý¤£À´¸Ì­±¸ê®Æ¤°»ò¬O­«ÂФ°»ò¤£¬O­«ÂÐ...
¦b§Ú¬Ý°_¨Ó«ç»ò¨C­Ó³£«Ü¹³...
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 8# ­ã´£³¡ªL


  ÁÂÁ¨â¦ìªº¦^ÂСI

Ãö©ó­ã¤jªº«Øij §Ú¥¿¦b°Ñ¦Ò±zªº³¡¸¨®æ¬ã¨s¥i¥H«ç»ò­×¥¿

³oµ{¦¡­ì¥»¦b2013¶]±oÆZ¶¶ªº §ï¦¨2003´N¤@°ïª¬ªp §Ú¤]«Ü­W´oQQ ¥u¯à«ùÄòºN¯Á¤F

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD