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

filter«¬ºA¤£²Å¦X

filter«¬ºA¤£²Å¦X

§Ú¥ý±N¦U­Ó¦aÂI°µ¦¨¤@­Ó°}¦C
µM«á±NSheet1¸Ì­±ªº¦aÂI»P°}¦C°µ¤ñ¹ï
­Y²Å¦X¸Ó¦aÂI¡A«h±N¸Ó¦aÂI¾ã¦Cªº¸ê®Æ½Æ»s¨ì¸Ó¦aÂIªº¤À­¶
¦ý§Ú°µ¨ì¤@¥b´N¥d¦í¤F
¿ù»~°T®§¬O«¬ºA¤£²Å¦X

book1.zip (8.43 KB)

code.zip (378 Bytes)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-1 02:48 ½s¿è

¦^´_ 1# ssooi


¦]¬°§AªºFilter ¥Îªk¿ù°Õ~~

²Ä1­Ó°Ñ¼Æ­n©ñ¤J "°}¦C"¡A²Ä2­Ó°Ñ¼Æ­n©ñ¤J "¦r¦ê"

ªì¾ÇªÌ½Ðµ½¥Î "F1" ¨Ó¬d¥Îªk



Filter¥Îªk.png
2020-8-1 02:43



©Ò¥H¨Ì§Aªºµ{¦¡  Filter(a,all(i)) ­n§ï¦¨ Filter(all,a)

­è¦n¦³­Ó½d¨Ò¥i¥H¥ÎFilter¥Ü½dµ¹§A¬Ý¡A¦p¤UÃìµ²


http://forum.twbts.com/thread-8370-1-1.html

¤£¹L§Aªºµ{¦¡¤£¥u³o­Ó°ÝÃD......

¦³¤£¤Ö»yªk¿ù»~¡AÁÙ¦³ÅÞ¿è°ÝÃD......

§AªºIF¥i¯à¥Ã»·¶i¤£¥h......¶i¥h¤F¤]·|°õ¦æ¥¢±Ñ

¦A¥[ªo§a!
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

°ò¥»¾Þ§@¥\¯à--¿z¿ï, À³¥i³B²z³o­Ó°ÝÃD,
¥ý¨Ï¥Î¿ý»s¨ú±oµ{¦¡½X...¬ã¨s¤U...

¦³°ÝÃD¦A´£~~

TOP

¦U¦ì°ª¤â¡A¯uªººÉ¤O¤F~filter¤@ª½·d¤£©w¡A¥Î¥t¥~¤@ºØ¼gªk¡A¼g¨ì³o¸Ì¡A¤£ª¾¹D°ÝÃD¦b­þ¡AµLªk±N¾ã¦C½Æ»s¨ì«ü©w¤u§@ªíªº³Ì«á¤@Äæ¡AÁÂÁ¦U¦ì°ª¤â
  1. Sub °j°é1()

  2. All = Array("¤T°Ó", "»·ªF")

  3. For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
  4.    
  5.     For i = 0 To UBound(All)
  6.    
  7.         If Cells(r, 2) = All(i) Then
  8.             Rows(r).EntireRow.Copy
  9.             Sheets(All).Select
  10.             ActiveSheet.Paste
  11.             Exit For
  12.         End If
  13.         
  14.     Next
  15. Next

  16. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-8-1 19:23 ½s¿è
  1. §Ú¤]¬O·s¤â¶¶«K½m²ß¤@¤U ¤£¹L³o¬q¦n¹³­þ¸Ì©Ç©Çªº  ¦ý§Ú¬Ý¤£¥X¨Ó ½Ð¤j¤j­Ì À°À°¦£ ¸Ó¦p¦ó­×§ï ·Pı ¦n¹³°j°é ¤Ó¦h ¤£¥²­nªº¥N½X ¦n¹³«Ü¦h 0.0
  2. Public Sub ½m²ß¸ó¤u§@ªí°}¦C½m²ß()

  3.     For x = 2 To Sheets.Count
  4.         Sheets(x).Cells.Clear
  5.         Sheets(1).Rows(1).Copy Sheets(x).Rows(1)
  6.     Next x
  7.    
  8.     E = 2
  9.     A = Array("¤T°Ó", "»·ªF", "«H¸q")
  10.     K = Range(Cells(2, 1), Cells(2, 3).End(xlDown))
  11.     ReDim B(LBound(K) To UBound(K))
  12.     ReDim Preserve B(LBound(K) To UBound(K))

  13.     For x = 2 To Sheets.Count
  14.         For J = LBound(K) To UBound(K)
  15.             For I = LBound(A) To UBound(A)
  16.             If A(I) <> K(J, 2) Then E = 2
  17.                 If A(I) = K(J, 2) Then
  18.                 If Sheets(K(J, 2)).Cells(E, 1) <> "" Then E = E + 1
  19.                     Sheets(K(J, 2)).Cells(E, 1) = K(J, 1)
  20.                     Sheets(K(J, 2)).Cells(E, 2) = K(J, 2)
  21.                     Sheets(K(J, 2)).Cells(E, 3) = K(J, 3)
  22.                     E = E + 1
  23.                 End If
  24.             Next I
  25.         Next J
  26.     Exit For
  27.     Next x
  28.    
  29.     Erase A, K, B
  30.    
  31. End Sub
½Æ»s¥N½X

TOP

­è¤~¸ÕµÛ§â  For x = 2 To Sheets.Count °j°é§R°£ µ²ªGµo²{ ®Ú¥»¥Î¤£¨ì ........XD

TOP

ReDim B(LBound(K) To UBound(K))
ReDim Preserve B(LBound(K) To UBound(K))
Erase B

³o¬q¤]¤£¥Î ....XD

TOP

¦^´_ 5# °a¤ªºµ


ÁÂÁ½Ѧ찪¤â
§Ú¦¨¥\¤F
ÁÂÁÂ!

TOP

¦^´_ 8# ssooi


    E = E + 1 ³o¬q¤]¤£¥Î .....XD  ©êºp §Ú¤]¬O·s¤â§Ú­Ì¥i¥H¤¬¬Û¾Ç²ß

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-1 23:56 ½s¿è

¦^´_ 8# ssooi

®¥³ß®¥³ß¡A¤j·§¯à²q¥X§A­Ì¬O¥Î2­Ó°j°é°µ¤ñ¹ïªº¼gªk~

¤âÄo¤F¤@¤U¡A§Ú¤]¤½§G§Úªº¼gªk§a¡A¥u¦³"¤@­Ó°j°é"

¦pªG¥Î·Ç¤j´£¨ìªº ¤u§@ªí"¿z¿ï"¥\¯à¡AÀ³¸Ó·|¼gªº§ó²³æ

¨C­Ó¤À­¶¡A§Ú¦Û¤v¦h½Æ»s¤F"¼ÐÃD¦C"¡A³o¼Ë·Pı¤ñ¸û¥¿±`~

³o­ÓÃD¥Ø¥ÎG¤j³ßÅw¥ÎªºMatch¨ç¼Æ´N¥i¥H»´ÃP¸Ñ¨M¤F

¦pªG§A­Ì·Q­nÅýµ{¦¡§ó¥[§Ö¬½·Ç¡AMatch¨ç¼Æ­È±o§Aªá®É¶¡¬ã¨s¤@¤U¥Îªk

µ{¦¡¦p¤U


Sub Test0801()
All = Array("¤T°Ó", "»·ªF", "«H¸q")
Rn = [A1].End(4).Row
For R = 1 To Rn
  B = Application.Match(Cells(R, 2), All, 0)
  If Not IsError(B) Then '¦aÂI¬O§_¦bAll°}¦C¤º
    With Sheets(Cells(R, 2).Value)
      Ro = .Cells(Rows.Count, 1).End(3).Row '§ä¨C­¶³Ì¥½¦C
      If Ro = 1 And .[A1] = "" Then Range([A1], [A1].End(2)).Copy .[A1]     '½Æ»s¼Ð´£¦C
      Ro = Ro + 1: Range(Cells(R, 1), Cells(R, 1).End(2)).Copy .Cells(Ro, 1) '½Æ»s¸ê®Æ
    End With
  End If
Next
End Sub


Àɮצp¤U~

Test0801.rar (17.84 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD