Remove merged cell and make row 6 a header.
And change to code to
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, x, flg As Boolean
If Intersect(Target, Columns("c:i")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Not [isref(mylist!a1)] Then
With Sheets.Add
.Name = "MyList": .Visible = 2
End With
End If
For Each r In Intersect(Target, Columns("c:i"))
If r.Row > 6 Then
With r.EntireRow.Range("b1").Validation
.Delete: flg = False
x = Filter(Evaluate("if(c" & r.Row & ":ba" & r.Row & "<>"""",c6:ba6)"), False, 0)
If UBound(x) > -1 Then
With Sheets("mylist").Rows(r.Row)
.Clear
With .Cells(r.Row, 1).Resize(, UBound(x) + 1)
.Value = x
.Name = "mylist_" & r.Row: flg = True
End With
End With
End If
If flg Then .Add Type:=3, Operator:=xlEqual, Formula1:="=mylist_" & r.Row
End With
End If
Next
Application.EnableEvents = True
End Sub
Edit: Code
Bookmarks