Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim i%, j%, k%, arr, arrt() Application.EnableEvents = False If Target.Address = "$L$1" Then arr = Range("b2:h" & [b65536].End(3).Row) For i = 1 To UBound(arr) If arr(i, 7) = Target Then k = k + 1 ReDim Preserve arrt(1 To 7, 1 To k) For j = 1 To UBound(arr, 2) arrt(j, k) = arr(i, j) Next End If Next End If Range("o2:u65536").ClearContents Range("o2").Resize(UBound(arrt, 2), 7) = Application.Transpose(arrt) Application.EnableEvents = True End Sub |