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

[µo°Ý] VBA ½Ð°Ý¦r¼Æ+¦r¥À±Æ§Ç«ç»ò°µ

[µo°Ý] VBA ½Ð°Ý¦r¼Æ+¦r¥À±Æ§Ç«ç»ò°µ

¦p¹Ï©Ò¥Ü¡A½Ð°Ý­n¦p¦ó¹F¨ì¦r¼Æ¤Ö¨ì¦h¡A¦A¨Ì¦r¥ÀA-Z¶i¦æ±Æ§Ç

Â^¨ú.PNG
2020-10-14 14:07


ªþÀÉ test.rar (13.99 KB)

¥»©«³Ì«á¥Ñ ikboy ©ó 2020-10-14 16:36 ½s¿è
  1. Sub zz()
  2. Dim a, b, c As Object, d As Object, i&, j&, n&, k
  3. Set d = CreateObject("scripting.dictionary")
  4. a = Range("a2:a" & [a65536].End(3).Row).Value
  5. ReDim aa(1 To UBound(a))
  6. For Each b In a
  7.     n = Len(b)
  8.     d(n) = d(n) & "|" & b
  9. Next
  10. k = d.keys
  11. With Application
  12.     For i = 1 To d.Count
  13.         n = .Small(k, i)
  14.         t = Split(Mid(d(n), 2), "|")
  15.         Set c = CreateObject("system.collections.arraylist")
  16.         For Each b In t
  17.             c.Add b
  18.         Next
  19.         c.Sort
  20.         For Each b In c
  21.             j = j + 1
  22.             a(j, 1) = b
  23.         Next
  24.     Next
  25. End With
  26. [d2].Resize(j) = a
  27. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# ikboy
¤Ó¯«°Õ!!!
ÁÂÁ¤j¤j~

TOP

  1. Sub tt()
  2. Dim Arr, Brr, n&
  3. n = [A65536].End(xlUp).Row
  4. With [B2].Resize(n - 1)
  5.     .Formula = "=LEN(A2)"
  6.     .Value = .Value
  7. End With

  8. With Range([A1], [B65536].End(xlUp))
  9.     Brr = .Value
  10.     .Sort Key1:=.Item(2), Order1:=xlAscending, Header:=xlYes
  11.     Arr = .Value
  12.     .Value = Brr
  13. End With

  14. [d1].Resize(UBound(Arr)) = Arr
  15. Range("B2:B" & n) = ""
  16. End Sub
½Æ»s¥N½X

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

Sub TEST()
With Range([B1], [A65536].End(xlUp))
     .Columns(2) = "=LEN(A1)"
     .Sort Key1:=.Item(2), Order1:=xlAscending, _
           Key2:=.Item(1), Order2:=xlAscending, Header:=xlYes
     .Columns(2).ClearContents
End With
End Sub

TOP

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

¾Ç¨ì¤@­Ó«ä¦ÒÅÞ¿è

   ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ s13030029 ©ó 2020-10-16 10:46 ½s¿è

¦^´_ 6# ­ã´£³¡ªL
¾ã­ÓÁYµu«Ü¦h¬O¨S¿ù¡A¦ý¬O¦pªG§Ú®ÇÃäÄæ¤w¸g¦³¨ä¥L¸ê®Æ¤F¡A¨S¿ìªk¦b®ÇÃä­pºâLen()ªº¸Ü­n«ç»ò§ï???
PS.¦]¬°¦³¼g¨ä¥Lµ{¦¡½X¡A¤£¤è«K¦A°µÄæ¦ì§ó°Ê

TOP

¦^´_ 8# s13030029

ÅÞ¿è³£¤@¼Ë, Åܳq¤@¤U§Y¥i:
Xl0000145.rar (14.48 KB)

TOP

¦^´_ 9# ­ã´£³¡ªL
­ã¤j Sorry~ ¥i¯à§Ú¨S¦³»¡ªº«Ü¸Ô²Ó
´N¬O»¡§Ú¥u¦³³æ¯Â²£«~½s¸¹¨ºÄæ­n­«·s±Æ§Ç¡A¨ä¥LÄ檺¸ê®Æ¤£»Ý­n¤@°_ÅÜ°Ê~

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD