Is there any VBA code for automatic Row addition or deletion in excel,,, whenever unique value changes and there is data below that formulae?

Question

suppose i have 10 unique data in some column i recently have data in a10, now when i enter the unique formula in cell a2 it will show spill error as i already have data in a10, i want to make that dynamic so that the data which was initially on a10 automatically goes to next row by adding new row,,,in short new row must me added if it shows spill error. so i want that VBA code so that there is automatic rows addition or deletion whenever unique values increases or decreases....

And actually i have a raw code but i don't know what is error here,,,,could you please help me...thank you in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Dim myCell As Range
    Dim lastRow As Long
    Dim numRows As Long
    Dim i As Long
    
    Set myRange = Intersect(Target, Me.UsedRange)
    
    If Not myRange Is Nothing Then
        For Each myCell In myRange
            If InStr(myCell.Formula, "=UNIQUE(") > 0 Then
                lastRow = Me.Cells(Me.Rows.Count, myCell.Column).End(xlUp).Row
                numRows = myCell.CurrentArray.Rows.Count
                
                If lastRow + numRows > Me.Rows.Count Then
                    Me.Rows(lastRow + 1 & ":" & lastRow + numRows).Insert Shift:=xlDown
                End If
                
                For i = lastRow + numRows To lastRow + 1 Step -1
                    If Application.CountA(Me.Cells(i, myCell.Column)) = 0 Then
                        Me.Rows(i).Delete Shift:=xlUp
                    End If
                Next i
            End If
        Next myCell
    End If
End Sub