在我昨天提出的問題中，我得到了很好的答案。我被要求提供更多或更清晰的細節，說明我在一個單獨的執行緒中嘗試完成的任務。原始問題可以在這里查看：
uj5u.com熱心網友回復：
請嘗試下一個方法。它假設要由事件觸發的所有行在 B:B 列中都應該有一個字串模式，如“RUN”后跟 1、2、3 等。基于此，下面的解決方案將構建一個能夠在一個范圍內轉換的陣列，觸發事件的單個陣列：
- 請復制下一個
Sub能夠從事件呼叫中接收三個引數的改編版本：
Sub Hide_Global(firstR As Long, lastR As Long, sh As Worksheet)
Dim rng As Range, rngH As Range, arr, i As Long
Set rng = sh.Range("B" & firstR & ":B" & lastR)
rng.EntireRow.Hidden = False 'show all rows in the range
arr = rng.Value 'place the range in an array for faster iteration
For i = 1 To UBound(arr)
If arr(i, 1) = "" Then
If rngH Is Nothing Then 'set the range to keep the cells where the rows must be hidden
Set rngH = rng.Cells(i, 1)
Else
Set rngH = Union(rngH, rng.Cells(i, 1)) 'create a Union range for all occurrences
End If
End If
Next
'hide the rows at once:
If Not rngH Is Nothing Then rngH.EntireRow.Hidden = True
End Sub
- 請復制下一個事件而不是現有事件：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastR As Long, rng As Range
lastR = Me.Range("B" & Me.Rows.Count).End(xlUp).Row 'last row in column B:B
'build the range to trigger the event using triggeredRng function:
Set rng = triggeredRng(Me.Range("B1:B" & lastR))
If Not Intersect(Target, rng) Is Nothing Then 'let the event running only for changes in the appropriate rows:
Application.EnableAnimations = False: Application.ScreenUpdating = False 'some optimization
Me.Calculate 'let the formulae to be updated
'send to the rows hiding Sub the range to be processed limits:
Hide_Global Target.Row 3, Target.Row 19, Me
Application.ScreenUpdating = True: Application.EnableAnimations = True
End If
End Sub
上面的事件代碼呼叫以下函式，構建范圍以觸發它：
Function triggeredRng(rng As Range) As Range 'it returns the range able to trigger the event
Dim i As Long, k As Long, arr, arrRows, rngAddr As String, lastR As Long
lastR = rng.Rows.Count 'last range row
arr = rng.Value 'place the range in an array, for faster iteration
ReDim arrRows(UBound(arr)) 'reDim initially the array to be sure that there are enough place for expected elements
For i = 12 To lastR 'iterate between the array elements:
If Left(arr(i, 1), 3) = "RUN" Then 'if cells with a pattern starting with "RUN" exist:
arrRows(k) = i: k = k 1 'place the row number as an array element and increment k
End If
Next i
ReDim Preserve arrRows(k - 1) 'keep only the array not empty elements
rngAddr = "A" & Join(arrRows, ",A") 'make a string by joining the array in this way
Set triggeredRng = Me.Range(rngAddr).EntireRow 'build a discontinuous range using the above built string
End Function
上面（建議的）解決方案的邏輯是下一個：當存在事件的作業表中發生更改時，僅由 B:B 列中包含“RUN x”的行構建的范圍（其中 x = 1, 1, 3 等），將調節事件以處理特定范圍。根據解釋的規則，隱藏行的現有 Sub 已被修改，以便接受firstR和lastR引數。
可以通過創建一個串列驗證單元格來優化代碼，該單元格包含所有型別為“RUN x”的字串，以便在需要時輕松訪問它們。如果你認為有必要，我會告訴你如何做到這一點。
請測驗建議的解決方案并發送一些反饋。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/401419.html
上一篇：使用位置：固定在桌子上