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

[µo°Ý] "±Æ§Ç"/"§R°£¾ã¦C"¦³¨S¦³¨ä¥¦ªºµ{¦¡¼gªk?

[µo°Ý] "±Æ§Ç"/"§R°£¾ã¦C"¦³¨S¦³¨ä¥¦ªºµ{¦¡¼gªk?

¦U¦ì¥ý¶i ¤j®a¦n,
§Úªº¤u§@ªí¤¤±`­n¥Î¨ì±Æ§Ç/§R°£¾ã¦C ¥\¯à,¦ý¥u¦³¸ê®Æ¼W¥[,¤£¬O­n­×§ï¥¨¶°¤¤ªºµ{¦¡,¤£µM´N±o±N½d³ò©Ô¤j,ı±o³o¼Ëªº§@ªk³£¤£²z·Q.
½Ð°Ý¥H¤U¤GºØ¥\¯à,¬O§_¦³¨ä¥¦ªº¼gªk,¥i¥H¤£¥Î¤@ª½­×§ï½d³òªº?
a) ±Æ§Ç
Sub sorting()
    Windows("Pre-paid_HK Cost.xlsx").Activate
    Range("A7").Select
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A2:A1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E2:E1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort.SortFields.Add Key:= _
        Range("D2:D1000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("NONE NE").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
b) §R°£¾ã¦C¥i¨£Àx¦s®æ
   With Workbooks("Pre-paid_Format.xlsx").Sheets("NE")
     .Range("A:AB").AutoFilter Field:=24, Criteria1:="Taipei"       '¿z¿ï±ø¥ó
    Rows("2:1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp

¦^´_ 1# PJChen
(b) §R°£¾ã¦C¥i¨£Àx¦s®æ
With Workbooks("Pre-paid_Format.xlsx").Sheets("NE").Range("A:AB")
        .AutoFilter Field:=24, Criteria1:="Taipei"       '¿z¿ï±ø¥ó
        .CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
End With
±Æ§Ç ­n¶ÇÀÉ®× ¬Ý¬Ý

TOP

¦^´_ 2# GBKEE

Gª©¤j,
³Â·Ð§A¤F. VBA TEST 2.zip (27.77 KB)

TOP

¦^´_ 3# PJChen
¸Õ¬Ý¬Ý 2003ªº±Æ§Ç¤è¦¡
  1.     With Windows("Pre-paid_HK Cost.xlsx").Worksheets("NONE NE")
  2.         .Range("A1:K36").Sort Key1:=.Range("A2"), Order1:=xlAscending, Key2:=.Range( _
  3.         "E2"), Order2:=xlAscending, Key3:=.Range("D2"), Order3:=xlAscending, _
  4.         Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
  5.         xlTopToBottom, SortMethod:=xlStroke, DataOption1:=xlSortNormal, _
  6.         DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
  7.     End With
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE
G¤j,
·Q½Ð°Ý.Range("A1:K36") ªº¼gªk,¦³¨S¦³¥t¤@ºØ,¨Ò¦p:  A:K (§Ú¥u¬Oªí¹F·§©À,µ{¦¡½X§Ú¤£·|¼g)
¦]¬°¸ê®Æ·|¤£Â_¼W¥[,¥Î.Range("A1:K36") ¹ï§Ú¨Ó»¡¤£¬O¨º»ò²z·Q.

TOP

¦^´_ 5# PJChen
.Range("A1:K36")  -> .Range("A1").CurrentRegion  ´N¹³ ±µÀs ¤@ª½ÂX¥R³s±µ¦³¸ê®ÆªºÄæ¤Î¦C
CurrentRegion ÄÝ©Ê   ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò

TOP

¦^´_ 6# GBKEE

¯u¬O¤Ó·PÁ¤F!§Ú³ßÅw³o­Ó.

TOP

¦^´_ 6# GBKEE

§Ú¤S¨Ó¤F,±Ï©R!½ÐÀ°§Ú¬Ý¤@¤U!
§Ú¦³¦h­Ó¤u§@ªí­n¥Î¨ìsort,­è¤~§A¼gªº±Æ§Çµ{¦¡¦nªø,¦]¬°©Èº|¤F¬Y¬q¦r,©Ò¥H§ÚÀ°¥¦§@¤F¤À¬q,¥i¬O¥¦§¹¥þ¤£°_§@¥Î
         .Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending,
          Key2:=.Range("Z2"), Order2:=xlAscending, _
          Key3:=.Range("H2"), Order3:=xlAscending, _
          Key4:=.Range("G2"), Order4:=xlAscending, _
          Key5:=.Range("E2"), Order5:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= xlTopToBottom, SortMethod:=xlStroke, _
        DataOption1:=xlSortNormal, _
        DataOption2:=xlSortNormal, _
        DataOption3:=xlSortNormal, _
        DataOption4:=xlSortNormal, _
        DataOption5:=xlSortNormal

TOP

¦^´_ 8# PJChen

.Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending, _

¨Ï¥Îexcel¤º«Ø¤§±Æ§Ç¥\¯à,±Æ§ÇÁä¦Ü¦h3²Õ
±Æ§ÇÁ䦳3²Õ¥H¤W,­n¥Î¨ä¥L¤èªk

TOP

¦^´_ 9# register313

­ì¨Ó¦p¦¹,½Ð°ÝÁÙ¦³¤°»òµ{¦¡¼gªk,¤ñ¸û²Å¦X§Ú©Ò­nªº?¤£­n«ü©w¦C¦¬,¥u­nÄæ¦ì½d³ò,¥B¥i¦³5²Õ¥H¤W±Æ§Çªº?
¤£­nRange("A1:K36") ªº¼gªk,¦³¨S¦³¥t¤@ºØ,¨Ò¦p:  A:K

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD