Option Explicit
Dim MyStr As String, sStr As String, shName As String
Const endR = 2000, endC = 237, eNull = 5
Dim i As Long, j As Long, k As Long, s As Long, n As Long, nR As Long, iDong As Long
Dim ArrData(), Arr(), ArrKQ(), ArrS()
Sub TrichLoc()
With Application
.ScreenUpdating = False
End With
With Sheets("Sheet1")
ArrData = .Range(.Cells(4, 2), .Cells(endR, endC)).Value
End With
ReDim Arr(1 To UBound(ArrData, 1))
For i = 1 To UBound(ArrData, 1)
MyStr = "x"
For j = 1 To UBound(ArrData, 2)
If Len(ArrData(i, j)) > 0 Then
MyStr = MyStr & "x"
Else
MyStr = MyStr & Space(1)
End If
Next j
Arr(i) = MyStr
Next i
Erase ArrData()
ReDim ArrKQ(1 To UBound(Arr), 1 To 2)
s = 0
For i = 1 To UBound(Arr)
For k = eNull To 2 Step -1
sStr = "x" & Space(k) & "x"
If InStr(1, Arr(i), sStr) > 0 Then
s = s + 1
ArrKQ(s, 1) = i
ArrKQ(s, 2) = k
Exit For
End If
Next k
Next i
With Sheets("Sheet1")
ArrData = .Range(.Cells(4, 1), .Cells(endR, endC)).Value
End With
For j = 2 To eNull
shName = j
With Sheets(shName)
.Range(.Cells(4, 1), .Cells(endR, endC)).ClearContents
End With
n = 0
ReDim ArrS(1 To s, 1 To endC)
For i = 1 To s
iDong = ArrKQ(i, 1)
If ArrKQ(i, 2) = j Then
n = n + 1
For k = 1 To endC
ArrS(n, k) = ArrData(iDong, k)
Next k
End If
Next i
If n > 0 Then
With Sheets(shName)
.Range("A4").Resize(n, endC) = ArrS
End With
End If
Next j
Erase ArrData(), Arr(), ArrKQ(), ArrS
With Application
.ScreenUpdating = True
End With
End Sub