Board logo

標題: [發問] "排序"/"刪除整列"有沒有其它的程式寫法? [打印本頁]
作者: PJChen    時間: 2012-4-29 15:32     標題: "排序"/"刪除整列"有沒有其它的程式寫法?

各位先進 大家好,
我的工作表中常要用到排序/刪除整列 功能,但只有資料增加,不是要修改巨集中的程式,不然就得將範圍拉大,覺得這樣的作法都不理想.
請問以下二種功能,是否有其它的寫法,可以不用一直修改範圍的?
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) 刪除整列可見儲存格
   With Workbooks("Pre-paid_Format.xlsx").Sheets("NE")
     .Range("A:AB").AutoFilter Field:=24, Criteria1:="Taipei"       '篩選條件
    Rows("2:1000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
作者: GBKEE    時間: 2012-4-29 16:15

回復 1# PJChen
(b) 刪除整列可見儲存格
With Workbooks("Pre-paid_Format.xlsx").Sheets("NE").Range("A:AB")
        .AutoFilter Field:=24, Criteria1:="Taipei"       '篩選條件
        .CurrentRegion.Offset(1).SpecialCells(xlCellTypeVisible).Delete xlShiftUp
End With
排序 要傳檔案 看看
作者: PJChen    時間: 2012-4-29 17:07

回復 2# GBKEE

G版大,
麻煩你了. [attach]10738[/attach]
作者: GBKEE    時間: 2012-4-29 20:00

回復 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
複製代碼
作者: PJChen    時間: 2012-4-29 20:41

回復 4# GBKEE
G大,
想請問.Range("A1:K36") 的寫法,有沒有另一種,例如:  A:K (我只是表達概念,程式碼我不會寫)
因為資料會不斷增加,用.Range("A1:K36") 對我來說不是那麼理想.
作者: GBKEE    時間: 2012-4-29 20:59

回復 5# PJChen
.Range("A1:K36")  -> .Range("A1").CurrentRegion  就像 接龍 一直擴充連接有資料的欄及列
CurrentRegion 屬性   傳回 Range 物件，該物件代表目前的區域。目前區域是指以任意空白列及空白欄的組合為邊界的範圍
作者: PJChen    時間: 2012-4-29 21:08

回復 6# GBKEE

真是太感謝了!我喜歡這個.
作者: PJChen    時間: 2012-4-29 21:45

回復 6# GBKEE

我又來了,救命!請幫我看一下!
我有多個工作表要用到sort,剛才你寫的排序程式好長,因為怕漏了某段字,所以我幫它作了分段,可是它完全不起作用
         .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
作者: register313    時間: 2012-4-29 22:04

回復 8# PJChen

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

使用excel內建之排序功能,排序鍵至多3組
排序鍵有3組以上,要用其他方法
作者: PJChen    時間: 2012-4-29 22:10

回復 9# register313

原來如此,請問還有什麼程式寫法,比較符合我所要的?不要指定列收,只要欄位範圍,且可有5組以上排序的?
不要Range("A1:K36") 的寫法,有沒有另一種,例如:  A:K
作者: Hsieh    時間: 2012-4-29 22:19

回復 10# PJChen
2010版本是可以多鍵排序，你所錄的程式碼即可
若要以2003排序限制，可利用排序欄位順序，使用迴圈去排列即可，但這要你說明欄位順序
作者: PJChen    時間: 2012-4-29 22:31

回復 11# Hsieh
版大您好,我用的是Excel 2010 format:
剛才所錄製的排序為:
  1.     Windows("BCM Order_Format.xlsx").Activate
  2.     Sheets("PO").Select
  3.     Range("D2").Select
  4.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Clear
  5.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
  6.         "D2:D600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  7.         xlSortNormal
  8.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
  9.         "Z2:Z600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  10.         xlSortNormal
  11.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
  12.         "H2:H600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  13.         xlSortNormal
  14.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
  15.         "G2:G600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  16.         xlSortNormal
  17.     ActiveWorkbook.Worksheets("PO").AutoFilter.Sort.SortFields.Add Key:=Range( _
  18.         "E2:E600"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  19.         xlSortNormal
  20.     With ActiveWorkbook.Worksheets("PO").AutoFilter.Sort
  21.         .Header = xlYes
  22.         .MatchCase = False
  23.         .Orientation = xlTopToBottom
  24.         .SortMethod = xlPinYin
  25.         .Apply
複製代碼
G大寫的據說只能至3組,所以我修改為以下5組的方法就行不通?
  1. With Workbooks("BCM Order_Format.xlsx")
  2. With .Sheets("PO")
  3.          .Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending,
  4.           Key2:=.Range("Z2"), Order2:=xlAscending, _
  5.           Key3:=.Range("H2"), Order3:=xlAscending, _
  6.           Key4:=.Range("G2"), Order4:=xlAscending, _
  7.           Key5:=.Range("E2"), Order5:=xlAscending, _
  8.         Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= xlTopToBottom, SortMethod:=xlStroke, _
  9.         DataOption1:=xlSortNormal, _
  10.         DataOption2:=xlSortNormal, _
  11.         DataOption3:=xlSortNormal, _
  12.         DataOption4:=xlSortNormal, _
  13.         DataOption5:=xlSortNormal
  14.     End With
  15.     End With
複製代碼
請問依我想要的,還有什麼方法可以用?
作者: PJChen    時間: 2012-4-29 22:39

回復 11# Hsieh
補充:
我用錄製的感覺不實用,所要才想要用"欄位範圍"作為排序,而不要指定欄+列,希望能有更好的建議!
作者: register313    時間: 2012-4-29 22:53

回復 13# PJChen

2010版
  用8樓的程式即可
  程式第1行最後少了 空白 與 _
   .Range("A1").CurrentRegion.Sort Key1:=.Range("D2"), Order1:=xlAscending, _
   再試試看
或
  1. Sub xx()
  2. Ar = Array(5, 4, 3, 2, 1)            '從優先順序最低的欄位開始,要排幾欄皆可
  3. For i = 0 To UBound(Ar)
  4.   [A1].CurrentRegion.Sort Key1:=Cells(2, Ar(i)), Order1:=1, Header:=xlGuess  '1:遞增 2:遞減
  5. Next
  6. End Sub
複製代碼
作者: Hsieh    時間: 2012-4-29 23:05

回復 12# PJChen
2010與2003排序方法不同
你所上傳的檔案欄位與程式碼不符
依照大意修改如下
  1. Sub nn()
  2. With Workbooks("BCM Order_Format.xlsx")
  3.    With .Sheets("PO")
  4.    Set Rng = Union(.[D2], .[Z2], .[H2], .[G2], .[E2])
  5.     .AutoFilter.Sort.SortFields.Clear
  6.     For Each A In Rng
  7.        .AutoFilter.Sort.SortFields.Add Key:=.Range(A, A.End(xlDown)) _
  8.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  9.         xlSortNormal
  10.     Next
  11.     With .AutoFilter.Sort
  12.         .Header = xlYes
  13.         .MatchCase = False
  14.         .Orientation = xlTopToBottom
  15.         .SortMethod = xlPinYin
  16.         .Apply
  17.     End With
  18.   End With
  19. End With
  20. End Sub
複製代碼
作者: PJChen    時間: 2012-4-29 23:24

回復 15# Hsieh
版大,
我自己試了一下,排序沒有成功,也想請問為何程式中不用帶出欄位的起迄?
附上檔案 [attach]10739[/attach]

回復 14# register313
Sorry!你寫的太精簡,我不知道怎麼跟8F的程式合在一起,請幫忙一下.
作者: PJChen    時間: 2012-4-29 23:25

回復 15# Hsieh
補充:
欄位A:AT   sort順序D.Z.H.G.E
作者: Hsieh    時間: 2012-4-29 23:41

回復 17# PJChen
  1. Sub nn()
  2. With Workbooks("BCM Order_Format.xlsx")
  3.    With .Sheets("PO")
  4.    Set b = .Range("A1").CurrentRegion
  5.    a = Array(4, 26, 8, 7, 5)
  6.     .AutoFilter.Sort.SortFields.Clear
  7.     For i = 0 To 4
  8.        .AutoFilter.Sort.SortFields.Add Key:=b.Columns(a(i)) _
  9.         , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
  10.         xlSortNormal
  11.     Next
  12.     With .AutoFilter.Sort
  13.         .Header = xlYes
  14.         .MatchCase = False
  15.         .Orientation = xlTopToBottom
  16.         .SortMethod = xlPinYin
  17.         .Apply
  18.     End With
  19.   End With
  20. End With
  21. End Sub
複製代碼
作者: PJChen    時間: 2012-4-29 23:51

回復 18# Hsieh
謝大,
成功了,這種程式的寫法都不用指明欄位範圍的是嗎? a = Array(4, 26, 8, 7, 5)部份,可以改用欄位嗎?要如何寫,我改為 a = Array(D,Z,H,G,E),就行不通了,
另外 Set b = .Range("A1").CurrentRegion是指從A1的欄位開始排序是吧?若排序的範圍是A5:Z200,是否應改為 Set b = .Range("A5").CurrentRegion
作者: Hsieh    時間: 2012-4-30 08:39

本帖最後由 Hsieh 於 2012-4-30 19:33 編輯

回復 19# PJChen
Set b = .Range("A1").CurrentRegion
是把變數b設成與A1儲存格所有相連的範圍
就是說從A1向右到整欄空白、向下到整列空白所形成的範圍，請看動畫。
[attach]10745[/attach]
所以，在2010版本中，篩選排序欄位是先新增所有排序層級後
再一次依照設定層級先後去排列(Apply)

a = Array(4, 26, 8, 7, 5)
改成a = Array(D,Z,H,G,E),就行不通了
這是因為D,Z,H,G,E這會視為5個變數D,Z,H,G,E，而不是這5個字母
a = Array("D","Z","H","G","E")
這樣才會把陣列元素讀為字母
作者: PJChen    時間: 2012-4-30 16:38

回復 20# Hsieh

版大,
了解,謝謝你的解說.



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)