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

[µo°Ý] (¤w¸Ñ¨M)¨Ï¥Î¦Û°Ê¿z¿ï­×§ï¸ê®Æ

[µo°Ý] (¤w¸Ñ¨M)¨Ï¥Î¦Û°Ê¿z¿ï­×§ï¸ê®Æ

¥»©«³Ì«á¥Ñ freeffly ©ó 2012-2-22 16:59 ½s¿è

¦pªG§Ú·Q¥Î¦Û°Ê¿z¿ïªº¤è¦¡¿ï¥X²Å¦Xªº±ø¥óµM«á¦A°µ­×§ï
µ{¦¡½X­n¦p¦ó¼g?

Sheet1ªº¸ê®Æ¬O­n­×§ïªº³¡¥÷
±ø¥ó´N¬O²Å¦XSheet2ªº«~¸¹(AÄæ)´N±NSheet1 AÄæ²Å¦Xªº³¡¥÷¹ïÀ³ªºBÄæ¸ê®Æ¤]­n¤@¼Ë
¦pªG¥Î°j°é¸ê®Æ¦h®É·|ªá«Ü¦h®É¶¡
¦Û¤vª¾¹Dªº¤èªk¥Ø«eÁÙ¤£ª¾¹D¦p¦ó¥Î¦Û°Ê¿z¿ïªº±¡ªp¤U­×§ï




·s¼WMicrosoft Excel ¤u§@ªí (2).rar (1.5 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 1# freeffly
¿ý»s·s¥¨¶°: ¦Û°Ê¿z¿ï ½m²ß ¸Õ¸Õ¬Ý

TOP

¸Õ¬Ý¬Ý
  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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# Hsieh

     ¤j¤jÁÂÁÂ
    SpecialCells(xlCellTypeVisible)   ­«ÂI¬O³o¦U¶Ü ¥i¨£ªºÀx¦s®æ
     
        ¨ä¥LªºÂ²²¤¤è¦¡Á٬ݤ£¤ÓÀ´
         
       ¥ý®ø¤Æ¤@¤U¦n¶i¤@¨Bµo°Ý
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# Hsieh


    ª©¥D¤j¤H§Ú¬d¬Ý¤F´X¦¸¦³¤@¨Ç¤£¤Ó¤F¸Ñ
   Set d = CreateObject("Scripting.Dictionary")   ³o¦Uªº·N«ä¬O¤°»ò ()¸Ì­±ªº·N«ä¤S¬O¤°»ò?
     For Each a In .Range(.[A2], .[A2].End(xlDown)) ³o¥y¬°¤°»òRangeªº«e­±­n¥[.()¸Ì­±ªº[]«e­±¤]­n¥[.?
    d(a.Value) = a.Offset(, 1).Value ³o¥y¤£¤ÓÀ´

   If .AutoFilterMode = False Then .Range("A:B").AutoFilter  ¨S¥Î¹L³oºØ¤è¦¡(.Range("A:B").AutoFilter)°µ¦Û°Ê¿z¿ï¦³¨S¦³­­¨î
   .Range("A:B").AutoFilter 1, ky ³o¥y¤£À´

   Set Rng = .AutoFilter.Range.Offset(1, 0).Columns(2).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants) ³o¥yªº·N«ä?

   «Ü©êºp©ì³o»ò¤[¤~´£°Ý,¦Ó¥BÁٰݤF³o»ò¦h¡A§Æ±æª©¥DÀ°¦£¦^µª¨Ì¤U
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 3# Hsieh


    Selection.AutoFilter Field:=14, Criteria1:="<>1"
    Range("N5:N" & Range("C65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).ClearContents

   ½Ð°Ý§Ú¥Î³oºØ¤è¦¡¦³¹F¨ì§Ú­nªºµ²ªG
  ¦ý¬O¤£½T©w¬O¤£¬O¥¿½Tªº¥Îªk
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh


    ÁÂÁª©¥D¤j¤H¦^ÂÐ
   ÁöµM¤w¸g¥Î¤¤¤å¸Ñ»¡
   ¦ý¬O¦³¤@¨ÇªF¦è¨S·§©À
   ¥ý¦¬¤U¾Ç²ß
   ·P®¦
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

§Ú¨Ï¥Î«á
For Each a In .Range(.[A2], .[A2].End(xlDown))
³o¤@¥y·|µo¥Í¿ù»~(¦¹³B»Ý­nª«¥ó)
½Ð°Ý¬O­þ¥X¤F°ÝÃD?
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 9# bgtsai


    §Ú¨Ï¥Î¬O¥i¥H¹B§@
   §A­n¤£­nªþ¤WÀɮ׬ݬÝ
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : °ß¨ä´L­«¦Û¤vªº¤H¡A¤~§ó«i©óÁY¤p¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD