標題: [發問] 如何縮短判斷條件刪除速度? [打印本頁]

---

作者: honching99    時間: 2023-12-30 07:53     標題: 如何縮短判斷條件刪除速度?

請問各位大大
目前有個統計表
要刪除所包含列中空白 .指定文字.相似文字等條件的資料
但似乎跑的時間有點久  不知可否幫忙是否有可精進之處?
For i = [a5000].End(xlUp).Row To 6 Step -1
If Cells(i, "A")="" _
Or Left(Cells(i, 1), 4) = "資料" _
Or Left(Cells(i, 1), 2) = "單位" _
Or Left(Cells(i, 1), 3) = "單 位" _
Or Left(Cells(i, 1), 4) = "單  位" _
Or Left(Cells(i, 1), 5) = "單   位" _
Or Left(Cells(i, 1), 6) = "單    位" _
Or Left(Cells(i, 1), 2) = "備註" _
Or Left(Cells(i, 1), 3) = "備 註" _
Or Left(Cells(i, 1), 2) = "幹事" _
Or Left(Cells(i, 1), 2) = "技士" _
Or Left(Cells(i, 1), 2) = "技佐" _
Or Left(Cells(i, 1), 2) = "組員" _
Or Left(Cells(i, 1), 2) = "課員" _
Or Left(Cells(i, 1), 5) = "治療師" _
Or Left(Cells(i, 1), 5) = "工作師" _
Or Left(Cells(i, 1), 5) = "治療師" _
Or Left(Cells(i, 1), 5) = "營養師" _
Or Left(Cells(i, 1), 3) = "助理" _
Or Left(Cells(i, 1), 3) = "佐理" _
Or Left(Cells(i, 1), 3) = "管理" _
Or Left(Cells(i, 1), 4) = "事務" _
Or Left(Cells(i, 1), 4) = "組長" _
Or Left(Cells(i, 1), 4) = "主任" _
Or Left(Cells(i, 1), 4) = "業務" _
Or Left(Cells(i, 1), 4) = "行政" _
Or Cells(i, 1).Value = "備註: 迄時欄位顯示" _
Or Cells(i, 1).Value = "職稱" _
Or Left(Cells(i, 1), 2) = "查詢" Then
    Rows(i).Delete
End If
Next i

---

作者: Andy2483    時間: 2023-12-30 10:04

本帖最後由 Andy2483 於 2023-12-31 22:09 編輯

回復 1# honching99


    謝謝論壇,謝謝前輩發表此主題
後學藉此帖練習InStr(),Mid(),Union(),學習方案如下,請前輩參考

Option Explicit
Sub TEST_S()
Dim A$, N%, i&, j%, M$, xU As Range, T$, xR As Range
A = "/資料/單位/單 位/單  位/單   位/單    位/備註/備 註/幹事/技士/技佐/組員/課員/治療師/工作師/" & _
    "營養師/助理/佐理/管理/事務/組長/主任/業務/行政/查詢/"
For i = 6 To [A5000].End(xlUp).Row
   Set xR = Cells(i, 1): T = Trim(xR): If InStr("/職稱//", "/" & T & "/") Then GoTo i01
   For j = 1 To Len(T)
      M = Mid(T, 1, j): N = InStr(A, M)
      If N = 0 Then GoTo i02
      If Mid(A, N - 1, 1) = "/" And Mid(A, N + j, 1) = "/" Then GoTo i01
      If j = Len(T) Then GoTo i02
   Next
i01: If xU Is Nothing Then Set xU = xR Else Set xU = Union(xU, xR)
i02: Next
If Not xU Is Nothing Then xU.EntireRow.Select
End Sub

---

作者: honching99    時間: 2024-1-5 09:56

感謝指導修正程式   獲益良多

---

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