Hsieh

回復 1# s7659109

1. Sub ex()
   
2. Dim Ar(), A As Range, s&, k&
   
3. With Sheets("data")
   
4. k = .[A4].End(xlToRight).Column
   
5. For Each A In .Range(.[A5], .[A5].End(xlDown))
   
6.    mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, k))), Chr(10))
   
7.    If InStr(mystr, Sheets("search").[C1]) > 0 Then
   
8.    ReDim Preserve Ar(s)
   
9.    Ar(s) = Split(mystr, Chr(10))
   
10.    s = s + 1
    
11.    End If
    
12. Next
    
13. If s > 0 Then Sheets("search").[A3].Resize(s, k) = Application.Transpose(Application.Transpose(Ar))
    
14. End With
    
15. End Sub

複製代碼

Hsieh

回復 5# s7659109
你的問題是變動陣列大小時，將上限值先設與後設的差異
因為陣列起始值在沒有設定下是從0開始，若你是將s=s+1放在ReDim之前，那麼陣列上限是0的情況下
你卻要給陣列Ar(1)設定值，這樣就會出現超出陣列索引範圍的錯誤
底下適用儲存格Change事件供你參考

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$AB$2" Then
   
3. Range("A:A").EntireRow.Hidden = False
   
4. For Each A In Range([A5], [A5].End(xlDown))
   
5.   If InStr(Join(Application.Transpose(Application.Transpose(A.Resize(, 31))), Chr(10)), [AB2]) = 0 Then A.EntireRow.Hidden = True
   
6. Next
   
7. End If
   
8. End Sub

複製代碼