GBKEE

回復 5# sunnyso
你的程式改用自動篩選 試試看

1. Option Explicit
   
2. Private Sub Worksheet_Activate()
   
3.     Dim Sh As Worksheet, E As Integer
   
4.     Application.ScreenUpdating = False
   
5.     Application.EnableEvents = False
   
6.     AutoFilterMode = False
   
7.     E = Cells(Rows.Count, "A").End(xlUp).Row
   
8.     E = IIF(E = 5, 6, E)
   
9.     Range("A6:E" & E).Clear
   
10.     For Each Sh In Sheets(Array("DataSheet2", "DataSheet3", "DataSheet3"))
    
11.         Sh.UsedRange.Offset(1).Copy Cells(Cells(Rows.Count, "A").End(xlUp).Row + 1, "A")            '合併資料
    
12.     Next
    
13.     E = Cells(Rows.Count, "A").End(xlUp).Row
    
14.     Range("A5:E" & E).AutoFilter                '範圍設立,自動篩選.
    
15.     For E = 1 To 5
    
16.         Range("A5").AutoFilter E, , , , False   '自動篩選: 取消箭頭
    
17.     Next
    
18.     Application.EnableEvents = True
    
19.     Application.ScreenUpdating = True
    
20. End Sub
    
21. Private Sub Worksheet_Change(ByVal Target As Range)
    
22.     Dim M As Variant
    
23.     Application.EnableEvents = False
    
24.     If Target.Row = 4 And Target.Column <= 5 Then
    
25.         M = Split(Target, "#")
    
26.         If UBound(M) >= 1 Then   '
    
27.            '自動篩選: 在關鍵字詞前加入【#】將以OR來篩選該欄。
    
28.             Range("A5").AutoFilter Target.Column, "=*" & M(0) & "*", xlOr, "=*" & M(1) & "*"
    
29.         Else
    
30.             Range("A5").AutoFilter Target.Column, "=*" & Target & "*"
    
31.         End If
    
32.     End If
    
33.     Application.EnableEvents = True
    
34. End Sub
    
35. Private Sub Worksheet_SelectionChange(ByVal Target As Range)  '第4列輸入後返回第4列
    
36.     Application.EnableEvents = False
    
37.     If Target.Row = 5 And Target.Column <= 5 Then
    
38.         Selection.Offset(-1).Select         'Target.Offset(-1).Select
    
39.     End If
    
40.     Application.EnableEvents = True
    
41. End Sub

複製代碼