Option Explicit
Sub XYZ2()
Dim i&, j&, t&, k&, Z&, M&, Comau&, Kmau&, Lr&, Col&
Dim eRng As Range, Sh As Worksheet
Dim Arr(), KQK(), KQM(), SoLan(), TieudeC(), TieudeK()
Dim Dic As Object, Key
Dim Ketqua As Range
Application.ScreenUpdating = False
On Error Resume Next
Set Sh = Sheet1
Lr = Sh.Cells(Rows.Count, 1).End(xlUp).Row
ReDim KQM(1 To Lr - 3, 1 To 100)
ReDim KQK(1 To Lr - 3, 1 To 100)
ReDim TieudeC(1 To 1, 1 To 100)
ReDim TieudeK(1 To 1, 1 To 100)
For i = 4 To Lr
t = t + 1
Set eRng = Sh.Range(Cells(i, 1), Cells(i, Sh.Range("A" & i).End(xlToRight).Column))
Col = eRng.Columns.Count
For j = Col To 1 Step -1
If eRng(1, j) <> Empty Then
If eRng(1, j).Interior.Color = vbYellow Then
Comau = Comau + 1
Else
Kmau = Kmau + 1
If Comau >= 1 Then Exit For
End If
End If
Next j
TieudeC(1, Comau) = "Liên tuc có màu liên tiêp " & Comau
TieudeK(1, Kmau - 1) = "Liên tuc không có màu liên tiêp " & Kmau - 1
KQM(t, Comau) = eRng(1, Col)
KQK(t, Kmau - 1) = eRng(1, Col)
If Comau > M Then M = Comau
Comau = 0: Kmau = 0: Set Rng = Nothing
Next i
Sh.[J1].Resize(10000, 1000).ClearContents
Sh.[J1].Resize(1, M) = TieudeC
Sh.[J1].Resize(2, M).Interior.Color = vbYellow
Sh.[J1].Offset(0, M).Resize(1, M) = TieudeK
Sh.[J4].Resize(t, M) = KQM
Sh.[J4].Offset(0, M).Resize(t, M) = KQK
Arr = Sh.Range("J4", "J4").Resize(t, M * 2).Value
ReDim SoLan(1 To 1, 1 To UBound(Arr))
For i = 1 To UBound(Arr, 2)
Set Dic = CreateObject("Scripting.Dictionary")
For j = 1 To UBound(Arr, 1)
If Arr(j, i) <> Empty Then
Key = Arr(j, i)
If Not Dic.Exists(Key) Then
k = k + 1: Dic.Add (Key), k
If SoLan(1, i) = Empty Then SoLan(1, i) = Key Else SoLan(1, i) = SoLan(1, i) & "," & Key
End If
End If
Next j
Set Dic = Nothing
Next i
Sh.[J2].Resize(1, UBound(Arr)) = SoLan
Application.ScreenUpdating = True
MsgBox "OK!", vbInformation, "THÔNG BÁO"
End Sub