Hsieh

¸Õ¬Ý¬Ý

1. Sub ex()
   
2. Dim Rng As Range
   
3. Application.ScreenUpdating = False
   
4. Set d = CreateObject("Scripting.Dictionary")
   
5. With ¤u§@ªí2
   
6.    For Each a In .Range(.[A2], .[A2].End(xlDown))
   
7.       d(a.Value) = a.Offset(, 1).Value
   
8.    Next
   
9. End With
   
10. With ¤u§@ªí1
    
11. If .AutoFilterMode = False Then .Range("A:B").AutoFilter
    
12. For Each ky In d.keys
    
13.    .Range("A:B").AutoFilter 1, ky
    
14.    Set Rng = .AutoFilter.Range.Offset(1, 0).Columns(2).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)
    
15.    If Rng.Count > 0 Then Rng = d(ky)
    
16. Next
    
17. .Range("A:B").AutoFilter
    
18. End With
    
19. Application.ScreenUpdating = True
    
20. End Sub

½Æ»s¥N½X

¦^´_ 1# freeffly

Hsieh

¦^´_ 5# freeffly


    Sub ex()
Dim Rng As Range
Application.ScreenUpdating = False
Set d = CreateObject("Scripting.Dictionary")  '³Ð«Ø¤@­Ó¦r¨åª«¥ó
With ¤u§@ªí2
   For Each a In .Range(.[A2], .[A2].End(xlDown))  '¦bA2¥H¤UÀx¦s®æ°µ°j°é
      d(a.Value) = a.Offset(, 1).Value  '¥Haªº­È§@¬°¯Á¤Þ¡Aaªº¥k°¼Àx¦s®æªº­È¥[¤J¦r¨åª«¥ó
   Next
End With
With ¤u§@ªí1
If .AutoFilterMode = False Then .Range("A:B").AutoFilter  '°²¦p¤u§@ªí«D¦Û°Ê¿z¿ïª¬ºA¡A´N°õ¦æA:BÄæ¸ê®Æ¦Û°Ê¿z¿ï
For Each ky In d.keys  '¥H¦r¨åª«¥óªº¯Á¤Þ°µ°j°é
   .Range("A:B").AutoFilter 1, ky    'A:BÄæ¦Û°Ê¿z¿ï¡A¥H²Ä¤@Äæ(AÄæ)¡A·Ç«h¬°¦r¨åª«¥ó¯Á¤Þ­È°µ¿z¿ï
   Set Rng = .AutoFilter.Range.Offset(1, 0).Columns(2).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants)  '¦Û°Ê¿z¿ï½d³ò¦V¤U¤@¦Cªº²Ä2Ä椤©Ò¦³¥i¨£Àx¦s®æ¨Ã¥B¬O«DªÅ®æªº³¡¤À½á¤©ÅܼÆRng
   If Rng.Count > 0 Then Rng = d(ky)  '¦pªGRng³o­Ó½d³òªºÀx¦s®æ¼Æ¶q¤j©ó¹s¡Aªí¥Ü¦³¦¹·Ç«h¸ê®Æ¡A´N±N³o¨ÇÀx¦s®æ¶ñ¤J¥Hky¬°¯Á¤Þ­Èªº¦r¨å¤º®e
Next
.Range("A:B").AutoFilter  '¸Ñ°£¦Û°Ê¿z¿ï
End With
Application.ScreenUpdating = True
End Sub