ªð¦^¦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
------------------------------------------------------------

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


    ÁÂÁ­ã¤j¡I «D±`·PÁ±zµL¨pªºÀ°§U §Ú¥»¨Ó¥¿¦b¹Á¸Õ¥Î±zºô¯¸ªº´£¨Ñªºµ{¦¡¨Ó­×§ï

µ²ªG´N¦³·sªº¦^ÂФF¡I §Ú¨Ó¸Õ¸Õ¬Ý¹ê»Ú¶]°_¨Ó¯à¤£¯à¦¨¥\!

TOP

Sheets(1).Cells(Me.Index + 1, 2) = [A3]
§ï¦¨¥H¤U¤~¹ï:
Sheets(1).Cells(Me.Index, 2) = [A3]

TOP

°ò¥»¬[ºc:
1) AH1 ©ñ¸ê®ÆÅÜ°ÊÀË´ú¤½¦¡, ­Y¦³ÅÜ°Ê, «h¥[¤@µ§·s°O¿ý,     
    ¨Ò¥~:B2:AG2¦³[¿ù»~­È]®É, ©Î B2 «D¼Æ­È(¨S¦³®É¶¡­È), «h¤£°O¿ý~~
2) ¦bB3:AG3¦P®É¦s©ñ³oµ§°O¿ý, ¨Ñ¤U¦¸¤ñ¹ï¬O§_ÅÜ°Ê
3) A3²Ö­pµ§¼Æ, ¦P®É°O¿ý¨ìMAINªí¬Û¹ïÀ³Àx¦s®æ

³o»ò¦hªí¦P®ÉIJµoµ{¦¡, ·|¤£·|¥´¬[.²£¥Í¿ù»~.©Îº|°O¿ý, ¤£±o¦Óª¾,
¦Û¦æ¥h´ú, ¨Ã¨Ì¹ê»Ú¹B¥Î¥h¦Û¦æ­×§ï¤½¦¡©Îµ{¦¡, ¯àÀ°ªº´N³o¼Ë:
DDE_main_01.rar (40.1 KB)

TOP

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


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

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

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

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

¥»©«³Ì«á¥Ñ ­ã´£³¡ª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

¦^´_ 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

¦^´_ 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

¦^´_ 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

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD